How to split string on two characters or delimiters in sql

Question

i'm trying to split this string "1,1_5,2_3,4" first on '_' and then split the sub string on ',' so it will be like "1,1_5,2_3,4" split-ed on '_' first so it will give this sub string "1,1" then this sub string split-ed on ',' and then insert the split-ed sub string "1 1" into temp table like this

INSERT INTO [dbo].[TEST] ([X],[Y])
                  VALUES (@X,@Y)

i did this with only split on one delimiter ',' like this first i create a table Split function

ALTER FUNCTION [dbo].[Split]
(
    -- Add the parameters for the function here
        @RowData nvarchar(2000),
        @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
    Id int identity(1,1),
    Data nvarchar(100)
)
AS
BEGIN
    -- Declare the return variable here
    Declare @Cnt int
    Set @cnt = 1

    While(charindex(@SplitOn,@RowData) > 0)
    Begin
        Insert Into @RtnValue (data)
        Select
            Data = ltrim(RTRIM(SUBSTRING(@RowData,1,CHARINDEX(@SplitOn,@RowData)-1))) 

            set @RowData = SUBSTRING(@RowData,CHARINDEX(@SplitOn,@RowData)+1,len(@RowData))
            set @Cnt = @Cnt + 1
    End
        insert into @RtnValue(data)
        select Data =  ltrim(RTRIM(@RowData))
    RETURN

END

then i create this stored procedure

ALTER PROCEDURE [dbo].[uspTest]
    -- Add the parameters for the stored procedure here
    @StringOFXIDs nvarchar(2000)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @SOMETABLE TABLE(ID INT IDENTITY(1,1) UNIQUE, XID INT);
    DECLARE @XCOUNT INT;
    DECLARE @XCURRENT INT;
    DECLARE @XCOUNTER INT = 1;
    -- Insert statements for procedure here
    INSERT INTO @SOMETABLE([XID])
    SELECT [Data] FROM [dbo].[Split](@StringOFXIDs,',');

    SELECT @XCOUNT = COUNT(1) FROM @SOMETABLE;
    WHILE (@XCOUNTER <= @XCOUNT)
    BEGIN 
        SELECT @XCOUNTER = [XID]
        FROM @SOMETABLE
        WHERE [ID]  = @XCOUNTER

        INSERT INTO [dbo].[TEST] ([X])
                          VALUES (@XCOUNTER)
        SELECT @XCOUNTER +=1;
    END
END

--EXEC [dbo].[uspTest] '1,2,3,4'

and then execute this stored procedure and every thing work but i can't figure out how to split the string on two characters or delimiters and then inserted to temp table thanks for any help in advance.

Your string splitter is the slowest of all the various ways of splitting string in t-sql. Check out this article for a number of ways of splitting strings that will be better for performance. — Sean Lange, Jan 14 '15 at 21:25
What would you expect for output based on your example of '1,1_5,2_3,4'? — Sean Lange, Jan 14 '15 at 21:30
http://stackoverflow.com/questions/21246446/split-string-in-column — Aaron Bertrand, Jan 14 '15 at 22:47

Hart CO · Accepted Answer · 2015-01-14T21:36:59.717

You can use XML functionality to split into rows, then split into columns:

DECLARE @string VARCHAR(100) = '1,1_5,2_3,4'
;WITH cte AS (SELECT RTRIM(LTRIM(Split.a.value('.', 'VARCHAR(100)'))) AS Txt
                     ,ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RN    
              FROM  (SELECT CAST ('<M>' + REPLACE(@string, '_', '</M><M>') + '</M>' AS XML) AS DATA
                    ) AS A 
                     CROSS APPLY Data.nodes ('/M') AS Split(a)
              )
     ,cte2 AS (SELECT CONVERT(XML,'<String><Section>'+ REPLACE(REPLACE(Txt ,'|',','),',', '</Section><Section>') + '</Section></String>') AS Txt
                      ,RN
               FROM cte
               )   
 SELECT Txt.value('/String[1]/Section[1]','varchar(100)') AS Col1
       ,Txt.value('/String[1]/Section[2]','varchar(100)') AS Col2
FROM  cte2
ORDER BY RN

Outputs:

Col1       Col2
---------------
1             1
5             2
3             4

How to split string on two characters or delimiters in sql

1 Answers1