Archives of the TeradataForum

Message Posted: Fri, 19 Jul 2002 @ 12:17:40 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi David and Claybourne ;-)

There is a fast way, but i prefer not to use a global temp table ;-)

First, you need a helper table with consecutive integers from 1 to 64000 in it.

With that helper table it's easy to 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 of integers, which is split into one row per integer and joined to sys_calendar.calendar. And the result set is sorted according to the order of values in inList without an ugly case statement ;-)

Dieter

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;

replace macro splitstring(inList varchar(64000))
as
(
select *
from

(SELECT
   n AS StartPos,
   CAST(
   SUBSTRING(:inList FROM n
   FOR (COALESCE((NULLIF(POSITION(','
                         IN SUBSTRING(:inList FROM n)),0)),64000)) - 1)
   AS int) AS StrPart
FROM nums
WHERE
  n BETWEEN 1 AND Char_Length(:inList)
AND
  (SUBSTRING(:inList FROM n - 1 FOR 1) = ','
   OR
   n = 1)
) tmp

join sys_calendar.calendar c
on c.calendar_date = tmp.StrPart
order by StartPos
;
);

exec splitstring(
 '210402,1251227,330605,121004,1300403,101101' ||
 ',1840707,60907,1710523,1070919,1791212,520211' ||
 ',400129,140223,471112,91119,1850218,516,210603' ||
 ',1070705,200609,611029,150302,1591105,20425' ||
 ',1620427,581016,120820,80720,230814,1540109' ||
 ',1680413,800308,420414,310820,1610120,100803' ||
 ',1530428,1541125,80615,1200722,580522,530724' ||
 ',1540307,1461023,571119,370604,610311,1830727'
);

	<Prev	Next>		<<First	<Prev	Next>	Last>>