how to get the comma separated values of the column stored in the Sql server

Question

how to get the comma separated values stored in the Sql Db into a individual values

e.g in sql DB the column is stored with comma values as shown below,

EligibleGroup

A11,A12,A13
B11,B12,B13

I need to get

EligibleGroup

A11
A12
A13
B11
B12
...

I have written a query that will fetch me some list of employees with employee name and eligible group

XXX  A11
YYY  B11
ZZZ  C11

I need to check that the employees(XXX,YYY,ZZZ) eligiblegroup falls within this

EligibleGroup

A11,A12,A13
B11,B12,B13

and return me only that rows.

Answer 1

use a "user defined function" like the one shown here (including source code) - it returns the splitted values as a "table" (one row per value) you can select from like

select txt_value from dbo.fn_ParseText2Table('A11,A12,A13')

returns

A11
A12
A13

Answer 2

You could use a subquery:

SELECT employee_name, eligible_group 
FROM YourTable 
WHERE eligible_group IN 
        (SELECT SPLIT(EligibleGroup) 
         FROM tblEligibleGroup 
         WHERE <some conditions here>)

I don't believe the "SPLIT" function exists in SQL Server so you'll have to either create a user defined function to handle that, or you could use the nifty workaround suggested here: How do I split a string so I can access item x?

Answer 3

Assuming that EligibleGroup has a fixed length data, you can try using SUBSTRING As follows:

select substring(EligibleGroup,1,3) from @test union all
select substring(EligibleGroup,5,3) from @test union all
select substring(EligibleGroup,9,3) from @test

This will return:

A11
A12
A13
B11
B12
...

You can try it in Data Explorer

And If you need to check if an employee fall into which EligibleGroup try this:

Select EligibleGroup from test where eligibleGroup like '%A11'

Answer 4

I think you may not have to split EligibleGroup. You can do another way by just:

select empId
from yourTempEmpTable t1, EligibleGroup t2
where t2.elibigle like '%'+t1.elibigle+'%'

I think it should work.

Answer 5

I think you can do it this way,

select left('A11,A12,A13',3) + SUBSTRING('A11,A12,A13',charindex(',','A11,A12,A13'),10)