T-SQL split string based on delimiter

Question

I have some data that I would like to split based on a delimiter that may or may not exist.

Example data:

John/Smith
Jane/Doe
Steve
Bob/Johnson

I am using the following code to split this data into First and Last names:

SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
       SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM   MyTable

The results I would like:

FirstName---LastName
John--------Smith
Jane--------Doe
Steve-------NULL
Bob---------Johnson

This code works just fine as long as all the rows have the anticipated delimiter, but errors out when a row does not:

"Invalid length parameter passed to the LEFT or SUBSTRING function."

How can I re-write this to work properly?

Diazotize

Answer 1

May be this will help you.

SELECT SUBSTRING(myColumn, 1, CASE CHARINDEX('/', myColumn)
            WHEN 0
                THEN LEN(myColumn)
            ELSE CHARINDEX('/', myColumn) - 1
            END) AS FirstName
    ,SUBSTRING(myColumn, CASE CHARINDEX('/', myColumn)
            WHEN 0
                THEN LEN(myColumn) + 1
            ELSE CHARINDEX('/', myColumn) + 1
            END, 1000) AS LastName
FROM MyTable

Chauchaucer

Answer 2

For those looking for answers for SQL Server 2016+. Use the built-in STRING_SPLIT function

Eg:

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'  

SELECT value  
FROM STRING_SPLIT(@tags, ',')  
WHERE RTRIM(value) <> '';  

Reference: https://msdn.microsoft.com/en-nz/library/mt684588.aspx

Below

I'm not sure this answers the question nor is it as useful as the CASE statement methods above. The STRING_SPLIT function turns it into a Table that is joined. So how does one get the first-position "First name" the second "Last Name" ? Using Cross-Apply transformed my ds into multiple rows. It's a great way transform String data into a table. Like the OP, I'm trying to transform a single column into two columns. STRING_SPLIT turns it into two rows and no way to tell which row is the "first" "second" "third" (unless Over works). But that becomes more complicated. – Turbosupercharger

Answer 3

Try filtering out the rows that contain strings with the delimiter and work on those only like:

SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
       SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM   MyTable
WHERE CHARINDEX('/', myColumn) > 0

Or

SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
       SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM   MyTable
WHERE myColumn LIKE '%/%'

Trenttrento

Answer 4

SELECT CASE 
        WHEN CHARINDEX('/', myColumn, 0) = 0
            THEN myColumn
        ELSE LEFT(myColumn, CHARINDEX('/', myColumn, 0)-1)
        END AS FirstName
    ,CASE 
        WHEN CHARINDEX('/', myColumn, 0) = 0
            THEN ''
        ELSE RIGHT(myColumn, CHARINDEX('/', REVERSE(myColumn), 0)-1)
        END AS LastName
FROM MyTable

Halothane

Answer 5

ALTER FUNCTION [dbo].[split_string](
          @delimited NVARCHAR(MAX),
          @delimiter NVARCHAR(100)
        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
  DECLARE @xml XML
  SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

  INSERT INTO @t(val)
  SELECT  r.value('.','varchar(MAX)') as item
  FROM  @xml.nodes('/t') as records(r)
  RETURN
END

Lovesick

Answer 6

I just wanted to give an alternative way to split a string with multiple delimiters, in case you are using a SQL Server version under 2016.

The general idea is to split out all of the characters in the string, determine the position of the delimiters, then obtain substrings relative to the delimiters. Here is a sample:

-- Sample data
DECLARE @testTable TABLE (
    TestString      VARCHAR(50)
)
INSERT INTO @testTable VALUES 
    ('Teststring,1,2,3')
    ,('Test')

DECLARE @delimiter VARCHAR(1) = ','

-- Generate numbers with which we can enumerate
;WITH Numbers AS (
    SELECT 1 AS N

    UNION ALL 

    SELECT N + 1
    FROM Numbers 
    WHERE N < 255
), 
-- Enumerate letters in the string and select only the delimiters
Letters AS (
    SELECT  n.N
            , SUBSTRING(t.TestString, n.N, 1) AS Letter
            , t.TestString 
            , ROW_NUMBER() OVER (   PARTITION BY t.TestString
                                    ORDER BY n.N
                                ) AS Delimiter_Number 
    FROM Numbers n
        INNER JOIN @testTable t
            ON n <= LEN(t.TestString)
    WHERE SUBSTRING(t.TestString, n, 1) = @delimiter 

    UNION 

    -- Include 0th position to "delimit" the start of the string
    SELECT  0
            , NULL
            , t.TestString 
            , 0
    FROM @testTable t 
)
-- Obtain substrings based on delimiter positions
SELECT  t.TestString 
        , ds.Delimiter_Number + 1 AS Position
        , SUBSTRING(t.TestString, ds.N + 1, ISNULL(de.N, LEN(t.TestString) + 1) - ds.N - 1) AS Delimited_Substring 
FROM @testTable t
    LEFT JOIN Letters ds
        ON t.TestString = ds.TestString 
    LEFT JOIN Letters de
        ON t.TestString = de.TestString 
        AND ds.Delimiter_Number + 1 = de.Delimiter_Number  
OPTION (MAXRECURSION 0)

Neoplasticism

Answer 7

The examples above work fine when there is only one delimiter, but it doesn't scale well for multiple delimiters. Note that this will only work for SQL Server 2016 and above.

/*Some Sample Data*/
DECLARE @mytable TABLE ([id] VARCHAR(10), [name] VARCHAR(1000));
INSERT INTO @mytable
VALUES ('1','John/Smith'),('2','Jane/Doe'), ('3','Steve'), ('4','Bob/Johnson')


/*Split based on delimeter*/
SELECT P.id, [1] 'FirstName', [2] 'LastName', [3] 'Col3', [4] 'Col4'
FROM(
    SELECT A.id, X1.VALUE, ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY A.id) RN
    FROM @mytable A
    CROSS APPLY STRING_SPLIT(A.name, '/') X1
    ) A
PIVOT (MAX(A.[VALUE]) FOR A.RN IN ([1],[2],[3],[4],[5])) P

Liquidation

Answer 8

These all helped me get to this. I am still on 2012 but now have something quick that will allow me to split a string, even if string has varying numbers of delimiters, and grab the nth substring from that string. It's quick too. I know this post is old, but it took me forever to find something so hopefully this will help someone else.

CREATE FUNCTION [dbo].[SplitsByIndex]
(@separator VARCHAR(20)  = ' ', 
 @string    VARCHAR(MAX), 
 @position  INT
)
RETURNS VARCHAR(MAX)
AS
     BEGIN
     DECLARE @results TABLE
     (id   INT IDENTITY(1, 1), 
      chrs VARCHAR(8000)
     );
     DECLARE @outResult VARCHAR(8000);
     WITH X(N)
          AS (SELECT 'Table1'
              FROM(VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) T(C)),
          Y(N)
          AS (SELECT 'Table2'
              FROM X A1, 
                   X A2, 
                   X A3, 
                   X A4, 
                   X A5, 
                   X A6, 
                   X A7, 
                   X A8), -- Up to 16^8 = 4 billion
          T(N)
          AS (SELECT TOP (ISNULL(LEN(@string), 0)) ROW_NUMBER() OVER(
                                                   ORDER BY
              (
                  SELECT NULL
              )) - 1 N
              FROM Y),
          Delim(Pos)
          AS (SELECT t.N
              FROM T
              WHERE(SUBSTRING(@string, t.N, LEN(@separator + 'x') - 1) LIKE @separator
                    OR t.N = 0)),
          Separated(value)
          AS (SELECT SUBSTRING(@string, d.Pos + LEN(@separator + 'x') - 1, LEAD(d.Pos, 1, 2147483647) OVER(
                     ORDER BY
              (
                  SELECT NULL
              ))-d.Pos - LEN(@separator))
              FROM Delim d
              WHERE @string IS NOT NULL)
          INSERT INTO @results(chrs)
                 SELECT s.value
                 FROM Separated s
                 WHERE s.value <> @separator;
     SELECT @outResult =
     (
         SELECT chrs
         FROM @results
         WHERE id = @position
     );
     RETURN @outResult;
 END;

This can be used like this:

SELECT [dbo].[SplitsByIndex](' ',fieldname,2) 
from tablename

Accentuate

Answer 9

I would protect the substring operation by always appending a delimiter to the test strings. This makes the parsing much simpler. Your code may now rely on finding the right pattern, and not need to cope with special cases.

SELECT SUBSTRING(myColumn + '/', 1, CHARINDEX('/', myColumn)-1) AS FirstName,
       SUBSTRING(myColumn + '/', CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM MyTable

It eliminates edge cases and conditionals and cases. Always add an extra delimiter at the end, then the challenge case is no problem.

Medium