Split the string in SQL Server

Question

How to split the string based on the character and stored to different variable in SQL Server 2012?

For example:

declare @listOfIDs varchar(1000)
declare @result1 varchar(50),@result2 varchar(50)
set @listOfIDs='id1==id2==id3'

etc

This string is to be split based on the '=='

So the result should be like that

• result1 should have id1
• result2 should have id2

Answer 1

Sorry

MySQL does not include a function to split a delimited string. Although separated data would normally be split into separate fields within a relation data, spliting such can be useful either during initial data load/validation or where such data is held in a text field.

But you can emulate it

CREATE FUNCTION strSplit(x varchar(255), delim varchar(12), pos int) returns varchar(255)
return replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '');

select strSplit("aaa,b,cc,d", ',', 2) as second;
+--------+
| second |
+--------+
| b      |
+--------+

select strSplit("a|bb|ccc|dd", '|', 3) as third;
+-------+
| third |
+-------+
| ccc   |
+-------+

P.S. search 'split' at http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

Answer 2

Oh, If you change your comment - please notify users which try to help you

For MS SQL look here

CREATE FUNCTION dbo.SplitStrings_CTE
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS @Items TABLE (Item NVARCHAR(4000))
WITH SCHEMABINDING
AS
BEGIN
   DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);

   WITH a AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, 1), 0), @ll),
           [value] = SUBSTRING(@List, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, 1), 0), @ll) - 1)
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + @ld,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll),
           [value] = SUBSTRING(@List, [end] + @ld, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll)-[end]-@ld)
       FROM a
       WHERE [end] < @ll
   )
   INSERT @Items SELECT [value]
   FROM a
   WHERE LEN([value]) > 0
   OPTION (MAXRECURSION 0);

   RETURN;
END
GO

Answer 3

Use this code to split the string.

DECLARE @listOfIDs VARCHAR(1000)
DECLARE @result1   VARCHAR(50),
        @result2   VARCHAR(50),
        @delimiter VARCHAR(5)='=='

SET @listOfIDs='id1==id2==id3'

SELECT Split.a.value('.', 'VARCHAR(100)') Split_value
FROM   (SELECT Cast ('<M>'
                     + Replace(@listOfIDs, @delimiter, '</M><M>')
                     + '</M>' AS XML) AS Data) AS A
       CROSS APPLY Data.nodes ('/M') AS Split(a) 

Result:

Split_value
-----------
id1
id2
id3