How To return String into Delimited String in SqlServer and With Good Example Using "IN Clause"

As we know sql queries are Dynamic and Nondynamic queries.This example is for Non dynamic query.


For spliting string into delimited string we use Function in sql server .


Ex: @string nvarchar(max),
       @splitedstring nvarchar(max)
       set @string='word1,word2 '
       Result:@splitedstring ='word1','word2'




For this we created function in sql server

CREATE FUNCTION [dbo].[StrinToStringList]
( @str nVARCHAR (MAX), 
  @delimeter nvarchar(2))
   RETURNS 
    @result TABLE (
        [ID] nvarchar(max) NULL)
AS
BEGIN

    DECLARE @x XML 
    SET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'

    INSERT INTO @result
    SELECT DISTINCT x.i.value('.', 'nvarchar(max)') AS token
    FROM @x.nodes('//t') x(i)
    ORDER BY 1

RETURN
END


As we created function it returns in the form of Table result set and it takes 2 parametersfrom stored procedures first one is for "string to split" second is for delimited string which should be seperated from string.In our example shown above we pass "," comma delimeter so function will seperate string where ever comma is appeared in string.
Example:

Suppose we have a table as shown in below




If we want specific rows like say "aaa and ffff" using stored procedure as shown below.

create procedure CompanyFilter
(
@string nvarchar(max)='aaa,bbb'
)
as
begin

select * from table where names in(@string)

end


After finishing creating stored procedue execute that we get result like this as shown below




In stored procedure "@string" parameter acts as single string for qurey so table does not contains name with "aaa,bbb" thats why table returns nothing.

So re-modify the stored procedure as shown below

create procedure CompanyFilter
(
@string nvachar(max)='aaa,bbb
)
as
begin
Select * from table where names in (select id from dbo.StrinToStringList(@string,','))
end


In above SP the query is calling function and passing 2 parameters ie string and delimeter to function.The function as i said it returns table result set to stored procedure so below screen will show result