String Parsing in TeraData 2

[DFW1999]

I need to parse a comma delimited string and insert each token into a new table.
For example this is a string which I am passing to a procedure as input parameter:
'ABC,DEF,GHI,JKL,MNO,PQR'
Any help would be appreciated.
thanks

 

[Patten]

select
substring ('abc,def,ghi,jkl,mno,pqr' from 0 for position (',' in 'abc,def,ghi,jkl,mno,pqr'))
,substring ('abc,def,ghi,jkl,mno,pqr' from position(',' in 'abc,def,ghi,jkl,mno,pqr') +1 for position (',' in 'abc,def,ghi,jkl,mno,pqr')-1)

and so on

 

[dnoeth]

First, you need a helper table with consecutive integers from 1 to 64000 (maximum size of a varchar) in it (i usually have that kind of table for different purposes):
CREATE TABLE Nums
( n INT NOT NULL PRIMARY KEY );

insert into nums
sel day_of_calendar
from sys_calendar.calendar
where day_of_calendar <= 64000;

With that helper table and a cross join you can split a long list of comma delimited values into single tokens.

Following macro is a modified version of an algorithm i used to normalize denormalized columns.
Parameter inList is a comma delimited list which is split into one row per token:

replace macro splitstring(inList varchar(64000))
as
(
SELECT
n AS StartPos
,SUBSTRINGinList FROM n FOR
(COALESCE((NULLIF(POSITION(','IN SUBSTRINGinList FROM n)),0)),64000)) - 1)
AS StrPart
FROM nums
WHERE
n BETWEEN 1 AND Char_LengthinList)
AND
(SUBSTRINGinList FROM n - 1 FOR 1) = ','
OR
n = 1)
ORDER BY StartPos;
);

exec splitstring('ABC,DEF,GHI,JKL,MNO,PQR');

*** Query completed. 6 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

StartPos StrPart
----------- -------------------------------------------
1 ABC
5 DEF
9 GHI
13 JKL
17 MNO
21 PQR

It's a bit sophisticated and you probably need some time to understand it, but it's much faster than using a SP and can be easily extended/modified ;-)

Dieter