CODEWITHSUNDEEP
sqlsql-server-2008split
Mr doubt
Mr doubt

Reputation: 65

How to get row value from comma's values

Below is my Table XYZ data

Sno  ProgramID    ProgramName
 1      1,2          CCE
 2      3,11         DDU

I want output like below passing programID 1

Sno  ProgramID    ProgramName

 1      1,2          CCE

I am trying query like below

select * from XYZ  where ProgramID in('1')

No result is coming from above query, my question is single value matching with in query. I dont want like query match with exact value

Upvotes: 0

Views: 55

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You have a broken data model. You can do what you want using LIKE:

select *
from XYZ 
where ',' + ProgramID + ',' like '%,1,%';

That said, you should understand why storing multiple values in a string is wrong:

  • Numbers should be stored as numbers, not strings.
  • Ids should have properly declared foreign key references, which you cannot do with a string.
  • SQL has poor string processing capabilities.
  • Queries cannot make use of indexes and partitions and probably confuse the optimizer.
  • Maintaining the string to avoid duplicates is tricky.
  • SQL has a great data type for storing lists. It is called a table not a string.

Upvotes: 2

Benson Lopez
Benson Lopez

Reputation: 21

You can use:

 Select *  from XYZ  WHERE (',' + Rtrim(ProgramID) + ',') LIKE '%,1,%'

Use TRIM to eliminate any Spaces at the start and end of the string.

Upvotes: 2

Related Questions