0

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

commutiy
  • 43
  • 2
  • 12
  • You need to elaborate your question.Provide more examples, because now your question doesn't make any sense, not to me at least.. If you post what you've tried so far we might be able to understand what you are trying to acheive – holder Sep 20 '18 at 11:25
  • 1
    Your database design is wrong. You would be better off normalizing your database design or you will keep running into troubles forever – GuidoG Sep 20 '18 at 11:38
  • @GuidoG this is just an example – commutiy Sep 20 '18 at 11:40
  • Still, you have more than one value in a single column. that is very bad practice. You should change the design of your database to avoid this – GuidoG Sep 20 '18 at 11:41
  • Possible duplicate of [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) – GuidoG Sep 20 '18 at 11:45
  • what version of sql server are you using ? From version 2016 you can use the STRING_SPLIT function – GuidoG Sep 20 '18 at 11:50

4 Answers4

1

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'
devzero
  • 2,510
  • 4
  • 35
  • 55
0

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.

Hary
  • 5,690
  • 7
  • 42
  • 79
0

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'
Eralper
  • 6,461
  • 2
  • 21
  • 27
0

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
Noldy Nayoan
  • 127
  • 1
  • 9