Function to split all the values after delimiter

Question

I have a select which return 100 rows. For one of the column for some rows I get normal varchar(2) values. But in some rows I get a string like this 1}33}47}48}50}86}98}UC}AL}MH}88

I want to split the above function after delimiter } and return as rows.

can any one please help me in this.

Thanks in advance.

http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str — Andrew Savinykh, Nov 12 '13 at 00:28
You should check out the response to the following: http://stackoverflow.com/questions/2647/split-string-in-sql — Brian Gilreath, Nov 12 '13 at 01:05
@Brian the accepted answer there is not exactly a model of good performance. Set-based approaches tend to perform better. — Aaron Bertrand, Nov 12 '13 at 01:53

score 1 · Accepted Answer · answered Nov 12 '13 at 02:02

First, create a simple string splitting function:

CREATE FUNCTION [dbo].[SplitString]
    (
        @List NVARCHAR(MAX),
        @Delim VARCHAR(255)
    )
    RETURNS TABLE
    AS
        RETURN ( SELECT [Value] FROM 
          ( 
            SELECT 
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
              FROM sys.all_objects) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
          ) AS y
        );

Then, you can use it in a CROSS APPLY:

DECLARE @x TABLE(id INT, string VARCHAR(32));

INSERT @x VALUES(1,'34'),(2,'12{43{65{88{43{12{90{CD');

SELECT x.id, f.Value
  FROM @x AS x
  CROSS APPLY dbo.SplitString(x.string, '{') AS f;

Results:

ID      Value
----    -----
1       34
2       12
2       43
2       65
2       88
2       43
2       12
2       90
2       CD

SQLfiddle demo

More on split functions (and better alternatives, if splitting strings coming from the application layer):

http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

Function to split all the values after delimiter

1 Answers1