REGEXP_SPLIT_TO_TABLE doesn't work

Analytics
Enthusiast

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

9 REPLIES
Enthusiast

Re: REGEXP_SPLIT_TO_TABLE doesn't work

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;

Enthusiast

Re: REGEXP_SPLIT_TO_TABLE doesn't work

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

Enthusiast

Re: REGEXP_SPLIT_TO_TABLE doesn't work

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.

Senior Apprentice

Re: REGEXP_SPLIT_TO_TABLE doesn't work

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.

Enthusiast

Re: REGEXP_SPLIT_TO_TABLE doesn't work

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

Senior Apprentice

Re: REGEXP_SPLIT_TO_TABLE doesn't work

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.

Teradata Employee

Re: REGEXP_SPLIT_TO_TABLE doesn't work

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

Senior Apprentice

Re: REGEXP_SPLIT_TO_TABLE doesn't work

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 :-)

Teradata Employee

Re: REGEXP_SPLIT_TO_TABLE doesn't work

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!