String Separator in SQL Server

 In this article we will see as how we can query the result from relational table when the input parameter is sending data in the form of string separated by a delimiter.  We will use Product table from AdventureWorks for this article.

To elaborate above scenario let's see an example:

Say we want to return some product Number from Product table and in normal condition our query would be something like this:

select ProductId,Name,ProductNumber from ADWD.saleslt.Product where ProductNumber in (FR-R92B-58,FR-R92R-58,HL-U509-R)

In case, if we wanted to send all these values in one parameter then we would need to separate the values on the basis of their delimiter like comma in this case. In order to accomplish this we would need to first create a string separator function that will just do the job

 

CREATE FUNCTION [dbo].[StringSeparator](@Value varchar(max), @separator char(1))      

returns @hold_data_table TABLE (returnval varchar(max))      

as      

begin      

    declare @i as int      

    declare @separator_index as varchar(8000)      

    select @i = 1      

        if len(@Value)<1 or @Value is null or @Value=''  return      

    while @i!= 0      

    begin      

        set @i = charindex(@separator,@Value)      

        if @i!=0      

            set @separator_index = left(@Value,@i - 1)      

        else      

            set @separator_index = @Value      

        if(len(@separator_index)>0) 

            insert into @hold_data_table(returnval) values(@separator_index)      

        set @Value = right(@Value,len(@Value) - @i)      

        if len(@Value) = 0 break      

    end  

return      

end 

GO

Once the function is created we can create the Procedure in which we will pass multiple values of Product Number and would separate  values using function bulit above to retrieve values as shown below:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[test_param]

@param nvarchar(max)

 

as

begin

 

set nocount on;

Declare @tr nvarchar(100)

Declare @SQL nvarchar(max)

select @SQL=''

select @SQL =@SQL+'select ProductId,Name,ProductNumber from ADWD.saleslt.Product where ProductNumber  in (select * from dbo.[StringSeparator]('+''''+@param+''''+','',''))'

 

END

exec sp_executesql @sql

 

On running above script and creating the procedure let’s execute it as below:

exec [test_param] @param='FR-R92B-58,FR-R92R-58,HL-U509-R'