Single separator to split the string


Every application has some database requirement where Application developer or Database developer or DBAs has to split the string in different ways as per the requirements. In this article I am sharing some string splitter functions to work on it.

Workaround
Input = Input String, Criteria separator, Conditional separator
Output = Returns the table which contains separated string for given inputs

CREATE FUNCTION [dbo].[SplitString]

(

      @StringToSplit    VARCHAR(4000),

      @Separator        VARCHAR(1)

)

RETURNS TABLE AS RETURN

(

      WITH indices AS

      (     SELECT 0 S, 1 E

            UNION ALL

            SELECT E, CHARINDEX(@Separator, @StringToSplit, E) + LEN(@Separator)

            FROM indices

            WHERE E > S

      )

      SELECT      SUBSTRING(@StringToSplit,S,

                  CASE WHEN E > LEN(@Separator) THEN e-s-LEN(@Separator) ELSE LEN(@StringToSplit) - s + 1 END) String

                  ,S StartIndex

      FROM  indices WHERE S >0

)
GO

How to execute = SELECT * FROM dbo.[SplitString] ('A,b,c', ',')
Advertisements

3 thoughts on “Single separator to split the string

  1. yyeeehhh.. bhadia hho!! sahi hai..
    acha i want a help in codein.. can i change the scroll bar ka style or make my costumized for my site… which should work in mozilla firefox.. wat say??

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