CODEWITHSUNDEEP
sql-server
commutiy
commutiy

Reputation: 41

How to get the data that contain in comma separated values column in sql

I want to pass one clm_id and want the data from table which contain that clm_id like i pass 2012(clm_id) and want output data which contain 2012 in comma sperated value also.

What i want is that in 1st table there is clm_id which is repeated i want the data which id having repeated value including in comma separated value.

like 

id     clm_id
1       2011,2012
2       2012,2013
3       2012

output want : 

id    clm_id
1      2012
2      2012 
3      2012

like id(1,2,3) that contain 2012 clm_id

i have query like SELECT * FROM table1 WHERE ( clm_id in ('2012')) but not get value from comma separated value

Upvotes: 0

Views: 549

Answers (4)

Noldy Nayoan
Noldy Nayoan

Reputation: 127

You can try this :

    declare @YourTable TABLE (id int,clm_id varchar(50))

    INSERT @YourTable SELECT 1,'2011,2012'
    INSERT @YourTable SELECT 2,'2012,2013'
    INSERT @YourTable SELECT 3,'2012'

    declare @SearchYear char(4)='2012' -- input search value

    SELECT 
    id 
    ,@SearchYear AS [clm_id]
    FROM @YourTable
    WHERE
    CHARINDEX(@SearchYear,clm_id,0)>0
    ORDER BY id

result :

id  clm_id
1   2012
2   2012
3   2012

Upvotes: 0

Eralper
Eralper

Reputation: 6612

String_Split is newest built-in function provided for such tasks

select
    id, t.[value] as clm_id
from CLM
cross apply STRING_SPLIT(clm_id,',') as t
where [value] = '2012'

Upvotes: 0

devzero
devzero

Reputation: 2670

Simple way using like (https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017) and case (https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017)

WITH tmp (id, clm_id) AS
  (
  SELECT 1, '2011,2012'
  UNION
  SELECT 2, '2012,2013'
  UNION
  SELECT 3, '2012'
  )

SELECT 
tmp.id,
CASE
WHEN  tmp.clm_id LIKE '%2012%'
    THEN '2012'
ELSE ''
END AS clm_id
 FROM tmp

If you have SQL server 2016 or later you can use a nifty new function: 

  SELECT 
    id, cs.value       
   FROM tmp CROSS APPLY
   STRING_SPLIT(tmp.clm_id,',') cs
   WHERE cs.value='2012'

Upvotes: 1

Hary
Hary

Reputation: 5818

Simple LIKE Query will work. Check here

select id, clm_id from test where clm_id like '%2012%'

The above will returns the filtered query and if you want just your search param as your select column, why can't you make it simple?

select id, '2012' from test where clm_id like '%2012%'

Anyhow, as GuidoG suggested you need to change your schema a bit.

Upvotes: 0

Related Questions