Splitting Column Values

• cmcilvoy

  Ten Centuries

  Points: 1015

  More actions

  July 12, 2002 at 11:00 am

  #78985

  I need to be able to split a comma-delimited string in a table column and do a select in another table against these values. For instance a value like 6,7,8

  i need to split into

  select * from tblTest

  where

  tblTest.Id = '6' or

  tblTest.Id = '7' or

  tblTest.Id = '8'

  thanks-

  chris

  
  </cm>

• Antares686

  SSC Guru

  Points: 125444

  More actions

  July 12, 2002 at 11:15 am

  #432620

  See my example in this thread

  http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=5386&FORUM_ID=8&CAT_ID=1&Topic_Title=How%20to%20add%20coulmn%20value%20addition%20on%20a%20condition&Forum_Title=T-SQL

  It will give you an idea of one optional way. Otherwise you may need to use a cursor.

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

• Ed Hellyer-352491

  Grasshopper

  Points: 17

  More actions

  July 25, 2006 at 4:35 pm

  #651501

  Try this.

   

  ALTER FUNCTION dbo.fnStringToTable(@CommaDelimList varchar(5000), @Delim char(1))

  --*********************************

  --Example;

  --select *

  --from  dbo.fnStringToTable('one, two, three, four', ',')

  --

  --Author: Ed Hellyer

  --Date:  Tuesday July 26th 2006

  --

  --*********************************

  RETURNS @List TABLE (Value varchar(100))

  AS

    BEGIN

   declare @Start int

   set @Start = 1

   while @Start is not null

     begin

    insert into @List(Value) select LTrim(RTrim(SubString(@CommaDelimList, @Start, abs(@Start - IsNull(NullIf(CharIndex(@Delim, @CommaDelimList, @Start + 1), 0), Len(@CommaDelimList) + 1)))))

    set @Start = NullIf(CharIndex(@Delim, @CommaDelimList, @Start + 1), 0) + 1

     end

   return

    END

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  July 26, 2006 at 2:39 am

  #651546

  DECLARE  @SQL VARCHAR(8000),

           @Field VARCHAR(100)

  SELECT   @Field = MyField -- This is where you get the "6,7,8" string...

  FROM     MyTable

  WHERE    SomeID = 31

  SELECT   @SQL = 'SELECT * FROM tblTest WHERE ID IN (' + @Field + ')'

  EXEC     (@SQL)

  ---

  N 56°04'39.16"
  E 12°55'05.25"