Wednesday, September 24, 2014

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))
    @result TABLE (
        [ID] nvarchar(max) NULL)

    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


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.

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'

select * from table where names in(@string)


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
Select * from table where names in (select id from dbo.StrinToStringList(@string,','))

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

No comments:

Post a Comment