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