Select Query to comma seperated string.

• nilesh k

  Default port

  Points: 1457

  More actions

  August 24, 2009 at 6:12 am

  #1043566

  Hi All,

  I guess it worked for me using

  Select * from table where ','+col+',' LIKE '%,2,%'

  Thanks SSC veteran 😀

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  August 24, 2009 at 6:57 am

  #1043586

  This maybe a bit over the top for what you require, but it does handle extra spaces in the string ....

  drop table #t1

  go

  create table #t1

  (

  RowId integer,

  ValueStr nvarchar(50)

  )

  go

  insert into #t1 values(1,'1, 2 , 3,4')

  insert into #t1 values(2,'4')

  insert into #t1 values(3,'25,26,27,28,29,30,31,32')

  insert into #t1 values(4,'2 ')

  go

  Drop function CutCsv

  go

  Create function CutCsv(@CSVString varchar(max))

  returns table

  as

  return(

  with Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),

  Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),

  Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),

  Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),

  Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),

  SpacesPos(sIndex)

  as

  (

  Select n+1

  from nums

  where n spacesPos.SIndex)-1

  from spacesPos

  )

  Select Item = ltrim(rtrim(substring(@CSVString,StartPos,(EndPos-StartPos))))

  from cteSpaceDelta

  where EndPos is not null

  )

  go

  Select RowId from #t1 cross apply dbo.CutCsv(ValueStr) ValuesOut

  where ValuesOut.Item = '2'

  go

  ---

  
  Clear Sky SQL
  My Blog[/url]

• nilesh k

  Default port

  Points: 1457

  More actions

  August 24, 2009 at 7:04 am

  #1043592

  Thanks David.

  Thanks To all.

• drew.allen

  SSC Guru

  Points: 76963

  More actions

  August 24, 2009 at 8:55 am

  #1043682

  David's solution is correct given the sample formats, but if the formatting is not consistent, you can modify it slightly to give the correct results. For example where there may be spaces or commas around the '2'

  Select * from table where ','+col+',' LIKE '%[, ]2[, ]%'

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• David Burrows

  SSC Guru

  Points: 65141

  More actions

  August 24, 2009 at 11:03 am

  #1043776

  drew.allen (8/24/2009)

  ---

  David's solution is correct given the sample formats, but if the formatting is not consistent, you can modify it slightly to give the correct results. For example where there may be spaces or commas around the '2'

  Select * from table where ','+col+',' LIKE '%[, ]2[, ]%'

  Drew

  Yep there are a variety of options depending on if and where the space(s) are, even this would work

  where ','+REPLACE(col,' ','')+',' LIKE '%,2,%'

  But the performance would probably be worse

  Far away is close at hand in the images of elsewhere.
  Anon.