RSS

Split string on multiple delimited pass function sql

23 Jul

In This Article , i will write function that  split the string based onmultiple delimiters pass  and also remove the Extra spaces around the string.

--Create this user deinfed function.
 
CREATE FUNCTION dbo.[UDF_Split_String_On_Multiple_Delimiters]
(
      @String VARCHAR(MAX),  -- Variable for string
      @delimiter VARCHAR(50) -- Delimiter in the string
)
RETURNS @Table TABLE(        --Return type of the function
Splitcolumn VARCHAR(MAX)
)
BEGIN
 
        DECLARE @Xml AS XML
        DECLARE @REVISED_STRING VARCHAR(MAX)
 
        ;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
        N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
        N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
        N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
        FROM N3 AS X, N3 AS Y)
 
        SELECT @REVISED_STRING=STUFF((SELECT '' + (Case When
                PATINDEX('%[' + @delimiter + ']%',SUBSTRING(@String,Nums.n,1)) >0
                Then ',' else LTRIM(RTRIM(SUBSTRING(@String,Nums.n,1))) end)
        FROM N4 Nums WHERE Nums.n<=LEN(@String)  FOR XML PATH('')),1,0,'')
 
        SET @Xml = cast(('<a>'+replace(@REVISED_STRING,
                ',','</a><a>')+'</a>') AS XML)
 
        INSERT INTO @Table SELECT A.value('.', 'varchar(max)')
                as [Column] FROM @Xml.nodes('a') AS FN(a)
 
RETURN
END
GO
 
Call Function (One way)
 
 SELECT * FROM dbo.[UDF_Split_String_On_Multiple_Delimiters] (String , multiple delimiter)
SELECT * FROM dbo.[UDF_Split_Based_On_Multiple_Delimiters]
('sajjad,ahmed ; waleed; Ali',',;:')
 
Call Function(Second Way)
 
 SELECT * FROM dbo.[UDF_Split_String_On_Multiple_Delimiters] (String , multiple delimiter)
SELECT * FROM dbo.[UDF_Split_Based_On_Multiple_Delimiters]
('sajjad[}]ahmed [}] waleed[}]Ali','[}]')
GO
 
Advertisements
 
Leave a comment

Posted by on July 23, 2014 in SQL Query

 

Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: