Note: Thank you for visiting my blog. However, this blog is not maintained actively anymore. This post is now also in my new blog. Feel free to leave a comment there.
In this article I'm going to discuss about how to convert a comma separated string to a table to enable it for set based operation.
In this article I'm going to discuss about how to convert a comma separated string to a table to enable it for set based operation.
Lets say we've a table which has the definition like this :
Create Table Employee (
EmpId Int,
EmployeeName varchar(100) )
Problem Statement
You need to retrieve Employee Name based on EmpId provided. Now it is easier to retrieve the data when you have the EmpIds separately and the same can be used as below :
Select * from Employee where EmpId in (10,21,32,43,54,74,78,47,56,12,68)
The above query treats every EmpId separately. Now what if you get all the EmpIds in a single comma separated string like : '10,21,32,43,54,74,78,47,56,12,68,'. In that case you can not write query as below:
Select * from Employee where EmpId in ('10,21,32,43,54,74,78,47,56,12,68,')
The above query will not treat the EmpIds separately, but as a single csv string as a result this query will produce an erroneous result or may not parsed at all.
