Sql Sever Split String function with special character support

The following the is the SQL server table valued function which will return the result as table. The input to the function is string delimited with the comma (for that matter any character which is is not part of the string it self) and the delimited character. This will also work even if the special characters present in the given input string.

1. Function

Create FUNCTION UdfSplitString
(
   @String       NVARCHAR(MAX),
   @SplitChar  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value(‘(./text())[1]’, ‘nvarchar(4000)’)
      FROM 
      ( 
        SELECT x = CONVERT(XML, ‘<![CDATA[‘ 
          + REPLACE(@String, @SplitChar, ‘]]><![CDATA[‘) 
          + ‘]]>‘).query(‘.’)
      ) AS a CROSS APPLY x.nodes(‘i’) AS y(i)
   );
GO


2. Select Statement

Select * from UdfSplitString(‘1,2,3,*@#$~!%^&*()-+=_{}|’,’,’)

You can also test the functionality from the following link. http://sqlfiddle.com/#!6/e667f/3/0

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

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