Selecting rows from sql table having multiple values in the field

Question

Here i have a table 'tbllead' which is having column'leademployees' which contains the values such as

(450,449,451)- row1 
(450,449,451)-row 2
(455,449,454,451)- row3
(450,449,451)-row 4 
(450)-row5 )

then how to get the rows if my query is

select *
from TblLead
where LeadEmployees=451

The desired result should be... row 25,26,27,28,29,31,32,33,34,35

Never, ever store data as comma separated items. It will only cause you lots of trouble. — jarlh
– jarlh, Commented Nov 17, 2017 at 12:05

Gordon Linoff · Accepted Answer · 2017-11-17 12:06:22Z

2

You should fix your data model. Storing lists of numbers as strings SQL is wrong:

Sometimes, you are stuck with someone else's really bad design decisions. Here is one thing you can use like:

where ',' + cast(LeadEmployees as varchar(255)) + ',' like '%,' + '451' + ',%'

answered Nov 17, 2017 at 12:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Artier · Accepted Answer · 2017-11-17 12:21:00Z

0

It is very common scenerio where employee manage employees erds easily available. May be this is helpfull

answered Nov 17, 2017 at 12:21

Artier

1,6832 gold badges10 silver badges25 bronze badges