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.
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
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.
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
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.
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
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
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
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!
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