CODEWITHSUNDEEP
sql-server
BenYeomans
BenYeomans

Reputation: 383

Singular Comma separated string into multiple strings for IN clause in SQL Server

I have a string that looks like this 'NRT, STH, WST, EST, SCT' stored as a variable parameter to pass to a stored procedure. However I want to use that parameter in an IN clause but obviously that won't work, so I want to split it up so I get this output:

'NRT', 'STH', 'WST', 'EST', 'SCT'

as supposed to the singular string.

I am sure there is a way to do this and after trying to find the solution on here and google I can't find the exact solution to this (sorry if there is one already on SO)

I've got close but I am stuck with the last element having a comma after it.

This is what I have so far

DECLARE @region NVARCHAR(MAX)
SET @region = 'NRT, STH, WST, EST, SCT'

Select *
From
    Table
Where 
    RegionId 
In (
    Select 
    Coalesce 
    (
     Case When @region = ''
     then Name
     Else 
     Name + ','
     End,''
     )
     From 
     dbo.splitstring(@region)
)

If I run the Coalesce with the split function on it's own this is the result:

So the IN clause will not work with this. Any Ideas on how to do this?

Upvotes: 0

Views: 60

Answers (2)

vamsi
vamsi

Reputation: 352

Create a function as shown below 

create  FUNCTION [dbo].[FunctionStringSplit](@stringname NVARCHAR(MAX),@separator nvarchar(1))
RETURNS TABLE
AS
RETURN
    WITH cte AS(
        SELECT CAST(0 AS BIGINT) AS FirstIndex,CHARINDEX(@separator,@stringname) charposition
        UNION ALL
        SELECT charposition+1,CHARINDEX(@separator,@stringname,charposition+1)
        FROM cte
        WHERE charposition>0
    )
    SELECT SUBSTRING(@stringname,FirstIndex,COALESCE(NULLIF(charposition,0),LEN(@stringname)+1)-FirstIndex) AS splitstring
    FROM cte

Then call the function in your select statement like this

 Select *
From
    Table
Where 
    RegionId IN
(
SELECT
  splitstring
FROM [dbo].[FunctionStringSplit] (@Region,',')
)

Upvotes: 1

Sandesh
Sandesh

Reputation: 1044

I hope your splitstring function convert CSV values into Rows. Please see below function.

CREATE FUNCTION [dbo].[SplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Then you can simply use below code to check for multiple values passed to stored proc.

Select *
From
    Table
Where 
    RegionId IN
(
SELECT
  splitdata
FROM [dbo].[SplitString] (@Region,',')
)

Upvotes: 1

Related Questions