-2

I wanted to share my findings here using a temp table and a WHILE clause. I separate a comma separated string of INT's to use in a IN statement.

Guzumba
  • 85
  • 1
  • 5
  • A comma separated string of ints can be used in an in statement. Why separate? – Twelfth Jan 03 '14 at 18:51
  • The proper way to "share your findings" is using a [Community Wiki](http://meta.stackexchange.com/questions/11740/what-are-community-wiki-posts). – Kermit Jan 03 '14 at 18:54
  • Use Community Wiki to spread the voice and share your findings – G21 Jan 03 '14 at 18:56

1 Answers1

-1
DECLARE @fk_ArtworkType_ID varchar(20) = '1, 2, 13' 
SET @fk_ArtworkType_ID = REPLACE(@fk_ArtworkType_ID, ' ', '')

DECLARE @ArtworkTypeIDTable TABLE (fk_ArtworkType_ID int)
DECLARE @Count AS INT = 0
SET @Count = LEN(@fk_ArtworkType_ID) - LEN(REPLACE(@fk_ArtworkType_ID, ',', '')) + 1
WHILE @Count > 0
BEGIN
    IF @Count <> 1
        BEGIN 
            INSERT INTO @ArtworkTypeIDTable VALUES(SUBSTRING(@fk_ArtworkType_ID, 0, CHARINDEX(',', @fk_ArtworkType_ID, 0)))
            SET @fk_ArtworkType_ID = SUBSTRING(@fk_ArtworkType_ID, (CHARINDEX(',', @fk_ArtworkType_ID, 0) + 1), LEN(@fk_ArtworkType_ID))
        END
    ELSE
        BEGIN 
            INSERT INTO @ArtworkTypeIDTable VALUES(@fk_ArtworkType_ID)
        END
    SET @Count = @Count - 1
END;
Guzumba
  • 85
  • 1
  • 5