Home > SQL > CSV, CDF string – Multiple In by string IN(“1,2,3,4,5″) etc

CSV, CDF string – Multiple In by string IN(“1,2,3,4,5″) etc

January 27th, 2009 fbis 
CREATE FUNCTION CSVTable(@Str varchar(7000))
returns @t TABLE (numberval int, stringval varchar(100), DateVal datetime)
AS
begin
 
declare @i int;
declare @c varchar(100);
 
SET @Str = @Str + ','
SET @i = 1;
SET @c = '';
 
while @i <= len(@Str)
begin
IF substring(@Str,@i,1) = ','
begin
INSERT INTO @t
VALUES (CASE WHEN isnumeric(@c)=1 THEN @c else NULL END,
rtrim(ltrim(@c)),
CASE WHEN isdate(@c)=1 then @c else NULL END)
SET @c = ''
end
else
SET @c = @c + substring(@Str,@i,1)
SET @i = @i +1
end
RETURN
end

Note that the above works for all datatypes — dates or text or numbers (ints only, but that could be fixed as well).

So, for example:

SELECT * FROM
People
WHERE People.Name IN (SELECT StringVal FROM dbo.CSVTable('Jeff,Bill,Pete,Eddy, John,Mike'))
 
SELECT * FROM
Numbers
WHERE Numbers.Number IN (SELECT numberval FROM dbo.CSVTable('1,2,34,5,5,6,7,8'))
 
SELECT * FROM
Dates
WHERE Dates.Date IN (SELECT dateval FROM dbo.CSVTable ('1/1/2000','1/2/2000','1/3/2000'))

Of course, the function works with @variables or fieldnames or any other expression:

SELECT * FROM dbo.CSVTable(@ListOfNames)
Categories: SQL Tags:
Comments are closed.