String_Split in SQL Server 2012

STRING_SPLIT in SQL Server 2012

Other approach is to use XML Method with CROSS APPLY to split your Comma Separated Data :

SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
FROM
(
    SELECT CAST(''+REPLACE(@ID, ',', '')+'' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a);

Result :

DATA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Example :

DECLARE @ID NVARCHAR(300)= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';
DECLARE @Marks NVARCHAR(300)= '0,1,2,5,8,9,4,6,7,3,5,2,7,1,9,4,0,2,5,0';
DECLARE @StudentsMark TABLE
(id    NVARCHAR(300),
 marks NVARCHAR(300)
); 
--insert into @StudentsMark 
;WITH CTE
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') id,
            ROW_NUMBER() OVER(ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM
     (
         SELECT CAST(''+REPLACE(@ID, ',', '')+'' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a)),
     CTE1
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') marks,
            ROW_NUMBER() OVER(ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM
     (
         SELECT CAST(''+REPLACE(@Marks, ',', '')+'' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a))
     INSERT INTO @StudentsMark
            SELECT C.id,
                   C1.marks
            FROM CTE C
                 LEFT JOIN CTE1 C1 ON C1.RN = C.RN;
SELECT *
FROM @StudentsMark;

Separate a value from string in SQL Server 2012

create the UFN_STRING_SPLIT function suggested by @Dave here link

create FUNCTION [dbo].[UFN_STRING_SPLIT]
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(max)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '' 
          + REPLACE(@List, @Delimiter, '') 
          + '').query('.')
      ) AS a 
      CROSS APPLY x.nodes('i') AS y(i)
   );

test your code:

select
    Left(Item, charindex('=', Item, 0) - 1) as FielName,
    right(Item, len(Item) - charindex('=', Item, 0)) as [Value]
from
    dbo.UFN_STRING_SPLIT('ReferenceId=678&MobileNo=226789&Pnumber=9878&SIB=121314', '&')

How to split a string from a table SQL Server 2012?

Taking into account your link, this can be done by slightly changing the query by adding Cross Apply.

Select e.ID, t.a
From #Example As e Cross Apply ( 
SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
FROM
(
    SELECT CAST(''+REPLACE(e.List, ',', '')+'' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a)) As t(a)

How to split string in SQL Server 2012?

I created a loop iterating through each set until the length of the string is reached. Then stuffed that into a stored procedure:

CREATE PROCEDURE [dbo].[SplitMyFunkyText] @Source nvarchar(MAX)
AS
    DECLARE @Results AS TABLE(Part NVARCHAR(22))
    DECLARE @I INT = 1

    WHILE @I < LEN(@Source)
    BEGIN   
        INSERT INTO @Results
            SELECT SUBSTRING(@Source, @I, 22)

        SET @I = @I + 23
    END

    SELECT * FROM @Results
GO

Here is a test using your example value:

DECLARE @Source AS NVARCHAR(230)

SET @Source = '123 870503-23-5370 021 456 830503-23-5371 031 789 870103-11-5372 041 654 870501-23-5373 051 321 880503-12-5374 061 987 870803-23-5375 071 109 870508-06-5376 081 174 810503-03-5377 091 509 870103-01-5378 101 687 870501-12-5379 131'

EXEC SplitMyFunkyText @Source

Here are the test results:

123 870503-23-5370 021
456 830503-23-5371 031
789 870103-11-5372 041
654 870501-23-5373 051
321 880503-12-5374 061
987 870803-23-5375 071
109 870508-06-5376 081
174 810503-03-5377 091
509 870103-01-5378 101
687 870501-12-5379 131

PER YOUR SECOND QUESTION: "how about if my splitting record are not in fixed position"

You originally indicated "fixed length" and that is much easier. For records NOT in fixed length the following will work as long as it's in groups of 3 with spaces in between:

CREATE PROCEDURE [dbo].[SplitMyFunkyText] 
    @Source nvarchar(MAX)
AS
    DECLARE @Results AS TABLE(Part NVARCHAR(22))

    DECLARE @I INT = 1
    DECLARE @NextLength INT = -1
    DECLARE @SubPart1 NVARCHAR(20)
    DECLARE @SubPart2 NVARCHAR(20)
    DECLARE @SubPart3 NVARCHAR(20)

    WHILE @I < LEN(@Source)
    BEGIN
        -- GET 1st SubPart -----------------------------------
        SET @NextLength = CHARINDEX(' ',@Source,@I)

        IF @NextLength > 0
            SET @SubPart1 = SUBSTRING( @Source, @I, @NextLength - @I)
        ELSE
            SET @SubPart1 = SUBSTRING( @Source, @I, LEN(@Source))

        SET @I = @I + LEN(@SubPart1) + 1;

        -- GET 2st SubPart -----------------------------------
        SET @NextLength = CHARINDEX(' ',@Source,@I)

        IF @NextLength > 0
            SET @SubPart2 = SUBSTRING( @Source, @I, @NextLength - @I)
        ELSE
            SET @SubPart2 = SUBSTRING( @Source, @I, LEN(@Source))

        SET @I = @I + LEN(@SubPart2) + 1;

        -- GET 3st SubPart -----------------------------------
        SET @NextLength = CHARINDEX(' ',@Source,@I)

        IF @NextLength > 0
            SET @SubPart3 = SUBSTRING( @Source, @I, @NextLength - @I)
        ELSE
            SET @SubPart3 = SUBSTRING( @Source, @I, LEN(@Source))

        SET @I = @I + LEN(@SubPart3) + 1;

        INSERT INTO @Results
            SELECT @SubPart1 + ' ' + @SubPart2 + ' ' + @SubPart3
    END

    SELECT * FROM @Results
GO

Here is a more condensed version that works with non-fixed length fields in groups of 3:

CREATE PROCEDURE [dbo].[SplitMyFunkyText] 
    @Source nvarchar(MAX)
AS
    DECLARE @Results AS TABLE(Part NVARCHAR(255))

    DECLARE @I INT = 1
    DECLARE @Start INT = 1

    WHILE @I > 0
    BEGIN
        SET @Start = @I
        SET @I = CHARINDEX(' ',@Source, 1+CHARINDEX(' ',@Source, 1+CHARINDEX(' ', @Source, @I + 1)))

        IF @I > 0
            INSERT INTO @Results 
                SELECT SUBSTRING(@Source, @Start, @I - @Start)
        ELSE
            INSERT INTO @Results 
                SELECT SUBSTRING(@Source, @Start, LEN(@Source))
     END

     SELECT * FROM @Results
GO

Split string in SQL server 2012 in a row

Assuming values 1-5. This can easily be done with a little XML in concert with a CROSS APPLY

If the number columns are variable, you would have to go DYNAMIC.

EDIT - Changed to nvarchar

Example

Declare @txt  nvarchar(max)='2450,10,54,kb2344,kd5433;87766,500,100,ki5332108,ow092827'

Select ID=A.RetSeq
      ,B.*
 From (
        Select RetSeq = Row_Number() over (Order By (Select null)) 
              ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'nvarchar(max)')))
        From  (Select x = Cast('' + replace(@txt ,';','')+'' as xml).query('.')) as A 
        Cross Apply x.nodes('x') AS B(i)
      ) A
 Cross Apply (
                Select Val1 = ltrim(rtrim(xDim.value('/x[1]','nvarchar(max)')))
                      ,Val2 = ltrim(rtrim(xDim.value('/x[2]','nvarchar(max)')))
                      ,Val3 = ltrim(rtrim(xDim.value('/x[3]','nvarchar(max)')))
                      ,Val4 = ltrim(rtrim(xDim.value('/x[4]','nvarchar(max)')))
                      ,Val5 = ltrim(rtrim(xDim.value('/x[5]','nvarchar(max)')))
                From  (Select Cast('' + replace(A.RetVal,',','')+'' as xml) as xDim) as B1
             ) B

Returns

ID  Val1    Val2    Val3    Val4        Val5
1   2450    10      54      kb2344      kd5433
2   87766   500     100     ki5332108   ow092827

Alternative of string_split Function on lower compatibility level

Here is how I have approached this in pre-string_split days, by converting the list to an XML string and then using SQL Server's XML support.

DECLARE @list varchar(255) = 'value1,value2,value3,value4,value5';

SELECT
    x.f.value( '.', 'varchar(50)' ) AS [value]
FROM ( 
    SELECT CAST ( '' + REPLACE ( @list, ',', '' ) + '' AS xml ) AS x 
) AS d
CROSS APPLY x.nodes( '//v/i' ) x( f );

RETURNS

+--------+
| value  |
+--------+
| value1 |
| value2 |
| value3 |
| value4 |
| value5 |
+--------+

You could convert this into an inline table-valued-function:

CREATE OR ALTER FUNCTION dbo.my_string_split (  
    @list varchar(1000), @delim varchar(1) = ','
)
RETURNS TABLE 
AS
RETURN (
    SELECT
        x.f.value( '.', 'varchar(50)' ) AS [value]
    FROM ( 
        SELECT CAST ( '' + REPLACE ( @list, @delim, '' ) + '' AS xml ) AS x 
    ) AS d
    CROSS APPLY x.nodes( '//v/i' ) x( f )
)
GO

To call it:

SELECT * FROM dbo.my_string_split( 'value1,value2,value3,value4,value5', ',' );

Split a string with varying factors in SQL Server

This is tagged with [sql-server-2012] - what a pity... With v2016+ you could call for STRING_SPLIT or even JSON methods... The following is a rather hacky approach but works - at least with your provided test data...

Create a mockup-table (please do this yourself the next time).

DECLARE @tbl TABLE(ID INT IDENTITY, YourString VARCHAR(100));
INSERT INTO @tbl VALUES
 ('["2","3","4"]')
,('["1","2","3"]')
,('[]')
,('["Select option B","Select option C","Select option D"]')
,('["Moderate","Heavy","Heavy, Big & Abnormal"]');

--This is the query:

SELECT t.ID
      --,t.YourString 
      ,C.Separated.value('text()[1]','nvarchar(max)') AS Parted
FROM @tbl t
CROSS APPLY(SELECT REPLACE(REPLACE(REPLACE(YourString,'"','"'),'["',''),'"]','')) A(replaced)
CROSS APPLY(SELECT CAST('' + REPLACE((SELECT A.replaced [*] FOR XML PATH('')),'","','') + '' AS XML)) B(casted)
CROSS APPLY B.casted.nodes('/x') C(Separated);

The idea in short:

First of all I use multiple REPLACE() to clean and harmonise your data. The second CROSS APPLY will then use XML to split up your strings by replacing each comma together with the quotes! with XML tags. Thus we can prevent splitting at internal commas. But before, we have to use FOR XML on the orginal string, to allow characters such as the & in Big & Abnormal. The rest ist rather easy XPath/XQuery.

The result

+----+-----------------------+
| ID | Parted                |
+----+-----------------------+
| 1  | 2                     |
+----+-----------------------+
| 1  | 3                     |
+----+-----------------------+
| 1  | 4                     |
+----+-----------------------+
| 2  | 1                     |
+----+-----------------------+
| 2  | 2                     |
+----+-----------------------+
| 2  | 3                     |
+----+-----------------------+
| 3  | []                    |
+----+-----------------------+
| 4  | Select option B       |
+----+-----------------------+
| 4  | Select option C       |
+----+-----------------------+
| 4  | Select option D       |
+----+-----------------------+
| 5  | Moderate              |
+----+-----------------------+
| 5  | Heavy                 |
+----+-----------------------+
| 5  | Heavy, Big & Abnormal |
+----+-----------------------+

T-SQL split string

I've used this SQL before which may work for you:-

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

and to use it:-

SELECT * FROM dbo.splitstring('91,12,65,78,56,789')

---

Related Topics