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?
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?
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.
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.
Of course this works, the 1st query will be:
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.
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.
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
SELECT * FROM DBC.UDFInfo WHERE functionname='REGEXP_SPLIT_TO_TABLE'
-> NumParameters :4
Any idea what is going on?
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
The result row type is:
• outkey = NUMERIC, VARCHAR
• token_ndx = INTEGER
• token = VARCHAR
So the example must be changed in TD14.10+
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 :-)
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?