时间戳

首页 > 解决方案 > How to get row value from comma's values

sql - 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

标签： sqlsql-server-2008split

解决方案

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.

推荐阅读