All Forums Analytics
Qionglinnewbie 3 posts Joined 09/13
07 Apr 2014
REGEXP_SPLIT_TO_TABLE doesn't work

Dear All,
 
I'm trying to parse a string into small pieces using REGEXP_SPLIT_TO_TABLE, but after trying different syntaxs I still cannot find out how it works. Can you please have a look and tell me what's wrong?

query :

SELECT TD_SYSFNLIB.regexp_split_to_table(' bnp,&abc&,dbc,@dbc@,NULL', ',', 'i');
 

error message :

Executed as Single statement.  Failed [3706 : 42000] Syntax error: Invalid usage - table function allowed only in FROM clause of a SELECT query. 

Elapsed time = 00:00:00.004 

 

STATEMENT 1: Select Statement failed. 

 

Any help is really appreciated.

 

Thank you.

Qiong

 
 
 

Raja_KT 1246 posts Joined 07/09
08 Apr 2014

Is this what you are looking for? An example from a document

 

CREATE TABLE split_table_latin(id integer, src varchar(100) character set latin, pattern varchar(100) character set latin, match varchar(100) character set latin);

 

SELECT * from table( regexp_split_to_table(split_table_latin.src,split_table_latin.pattern,split_table_latin.match)

returns (res varchar(100) character set latin)) as t1;

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Qionglinnewbie 3 posts Joined 09/13
14 Apr 2014

Hi,
Thanks for helping, I've tried all examples I can found on internet, this one incluede. It doesn't work. Could you please try this function yourself till it works then give me your example? 
 
Thanks

Raja_KT 1246 posts Joined 07/09
14 Apr 2014

Oops, a quick look for me also it does not work.
But I remember one nice guy works a lot on regex function. Maybe if it helps your requirement.
http://forums.teradata.com/forum/database/regular-expression-on-teradata-14-0
To be honest, I have not used regexp_split_to_table any where in any project.
So far , it is mostly converting simple  rows to columns, columns to rows.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
14 Apr 2014

Of course this works, the 1st query will be:

SELECT * 
FROM
   TABLE (REGEXP_SPLIT_TO_TABLE(' bnp,&abc&,dbc,@dbc@,NULL', ',', 'i')
   RETURNS (res VARCHAR(100) CHARACTER SET LATIN)) AS dt;

What's your TD release? If this is a VM you should switch to the latest version.

Dieter

Raja_KT 1246 posts Joined 07/09
14 Apr 2014

oh!!!! Dieter, My release is 14.10.00.02. So I should get the latest release????

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
15 Apr 2014

Hi Raja,
i don't know about 14.10, but in 14 the initial one was too close to 00.00 and quite buggy version :)
There's 14.10.01.01 available which is approx. 5 month later than the 14.10.00.02.

Dieter

bartoszm 1 post Joined 08/12
30 Apr 2014

Dieter
i have some other issue with REGEXP_SPLIT_TO_TABLE
my system version is:
SELECT * FROM dbc.dbcinfo;
->  1 VERSION 14.10.00.14
->  2 RELEASE 14.10.00.12
 
I am trying your example
SELECT * FROM TABLE (REGEXP_SPLIT_TO_TABLE(' bnp,&abc&,dbc,@dbc@,NULL', ',', 'i') RETURNS (res VARCHAR(100) CHARACTER SET LATIN)) AS dt;
and i have:
->  SELECT Failed. 9881: FUNCTION 'REGEXP_SPLIT_TO_TABLE' CALLED WITH an invalid NUMBER OR TYPE OF parameters
 
but:
SELECT * FROM DBC.UDFInfo WHERE functionname='REGEXP_SPLIT_TO_TABLE'
->  NumParameters :4
----
Any idea what is going on?
Bartosz

dnoeth 4628 posts Joined 11/04
30 Apr 2014

Hi Bartosz,
there was a change in TD14.10 to add an inputkey and two additional columns in output, of course this was not documented in the manuals :-)
But the TD15 manual are corrected:

Expressions passed to this function must have the following data types:
• inkey = NUMERIC, VARCHAR
• source_string = CHAR, VARCHAR, CLOB
• regexp_string = CHAR, VARCHAR (maximum size of 512 bytes)
• match_arg = VARCHAR

Result Type
The result row type is:
• outkey = NUMERIC, VARCHAR
• token_ndx = INTEGER
• token = VARCHAR

So the example must be changed in TD14.10+

SELECT * 
FROM
   TABLE (REGEXP_SPLIT_TO_TABLE(1,' bnp,&abc&,dbc,@dbc@,NULL', ',', 'i')
   RETURNS (id INTEGER, tokennum INTEGER, token VARCHAR(100) CHARACTER SET UNICODE)) AS dt;

This should also fix Raja's issue :-)

Dieter

gl255000 3 posts Joined 08/14
05 May 2015

Hi dnoeth is it possible to return a token bigger than a max size of varchar, because the return token seems to be that must has the same size as the source_string but the source string can be a clob?
So, my question is: Is it possible to return a token type clob or bigger than a varchar?
Thank you!

You must sign in to leave a comment.