2

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.

Improve this question

5 Answers 5

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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?

Improve this answer

1 Comment

I think OP is actually looking for someone to provide the split function, not so much to be told that he needs one.
0

I think you can do it this way,

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

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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'
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.