Parsing Comma Separated Parameter Inside IN Clause SQL Server

March 13, 2012 12:40 am
Andri Kasta Marengga

Parsing Comma Separated Parameter Inside IN Clause SQL Server

Parsing array parameter into SQL Stored Procedure isn’t as simple as it seen. The problem is SQL Server has not array parameter. To outsmart this problem, we can make a SQL Function to delimit our parameter before parsing it to Stored Procedurr, DataSet, etc.

Here’s a sample code of this SQL Function


CREATE FUNCTION [dbo].[fn_ParseDelimitedStrings](@String nvarchar(MAX), @Delimiter char(1))
RETURNS @Values TABLE
(
RowId int Not Null IDENTITY(1,1) PRIMARY KEY
,Value nvarchar(255) Not Null
)
AS
BEGIN
DECLARE  @startPos smallint
,@endPos smallint

IF (RIGHT(@String, 1) != @Delimiter)
SET @String = @String + @Delimiter
SET @startPos = 1
SET @endPos = CharIndex(@Delimiter, @String)
WHILE @endPos > 0
BEGIN
INSERT @Values(Value)
SELECT LTRIM(RTRIM(SUBSTRING(@String, @startPos, @endPos - @startPos)))
-- remove the delimiter just used
SET @String = STUFF(@String, @endPos, 1, '')
-- move string pointer to next delimiter
SET @startPos = @endPos
SET @endPos = CHARINDEX(@Delimiter, @String)
END
RETURN
END

Done! Now we have a powerful function to array parsing problem. Here’s sample code to use this function


SELECT * FROM Account WHERE Name IN (
SELECT Value FROM [fn_ParseDelimitedStrings]('abc,def,ghi,jkl', ',')
)

, , , ,

One Response to “Parsing Comma Separated Parameter Inside IN Clause SQL Server”

  1. escort new york November 27, 2012 at 3:19 pm #

    Howdy! Someone in my Myspace group shared this
    site with us so I came to look it over. I’m definitely loving the information. I’m bookmarking and
    will be tweeting this to my followers! Exceptional blog
    and brilliant style and design.

Leave a Reply to escort new york Click here to cancel reply.