CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2395176
user2395176

Reputation: 315

How to split a comma separated data

I have a procedure and input is comma separated like '1,2,3'.

I would like to query like 

SELECT * FROM PERSON WHERE PERSON_ID IN(1,2,3).

Please note that PERSON_ID is integer.

Upvotes: 1

Views: 243

Answers (4)

user359040
user359040

Reputation:

Here's a way of doing it using a recursive CTE:

declare @SearchList varchar(20)
set @SearchList= '1,2,3'
;with cte as
(select case charindex(',',@SearchList)
            when 0 then cast(@SearchList as int)
            else cast(left(@SearchList,charindex(',',@SearchList)-1) as int)
        end searchVal,
        case charindex(',',@SearchList)
            when 0 then ''
            else right(@SearchList,
                       len(@SearchList)-charindex(',',@SearchList) )
        end remainStr
 union all
 select case charindex(',',remainStr)
            when 0 then cast(remainStr as int)
            else cast(left(remainStr,charindex(',',remainStr)-1) as int)
        end searchVal,
        case charindex(',',remainStr)
            when 0 then ''
            else right(remainStr,
                       len(remainStr)-charindex(',',remainStr) )
        end remainStr
 from cte
 where remainStr > ''
)
select p.* 
from cte
join person p on cte.searchVal = p.person_id

SQLFiddle here.

Upvotes: 0

Kenneth Fisher
Kenneth Fisher

Reputation: 3812

I've seen this type of question so often I posted a blog on it here.

Basically you have three options (to the best of my knowledge)

The LIKE version that Gordon Lindoff suggested.

Using a split function like so.

DECLARE @InList varchar(100)
SET @InList = '1,2,3,4'

SELECT MyTable.*
FROM MyTable
JOIN DelimitedSplit8K (@InList,',') SplitString
    ON MyTable.Id = SplitString.Item

Or using dynamic SQL.

DECLARE @InList varchar(100)
SET @InList = '1,2,3,4'

DECLARE @sql nvarchar(1000)

SET @sql = 'SELECT * ' + 
            'FROM MyTable ' + 
            'WHERE Id IN ('+@InList+') '

EXEC sp_executesql @sql

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Because contains seems like overkill (it is designed for fuzzy searching and uses a full text index), because charindex() is not standard SQL, and I abhor answers where varchar does not have length, let me give an alternative:

SELECT *
FROM PERSON
WHERE ','+@SearchList+',' like '%,'+cast(PERSON_ID as varchar(255))+',%';

The concatenation of commas for @SearchList makes sure that all values are surrounded by delimiters. These are then put around the particular value, to prevent 1 from matching 10.

Note that this will not be particularly efficient, because it will require a full table scan.

Upvotes: 0

xagyg
xagyg

Reputation: 9711

SearchList = ',' + inputSearchTerm + ',';  /* e.g. inputSearchTerm is '1,2,3' */

SELECT * FROM PERSON WHERE CONTAINS(SearchList, ',' + cast(PERSON_ID as varchar) + ',');

Upvotes: 0

Related Questions