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

Answer 1

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

• Values should have the right data type
• Foreign key relationships should be properly defined
• SQL has poor string functions
• Such queries cannot be optimized very well

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' + ',%'

Answer 2

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