How to Robustly Split Strings in T-SQL Using Delimiters?

T-SQL string splitting: cleverly handle missing delimiters

Splitting strings based on delimiters can be tricky in T-SQL, especially when delimiters are missing in some rows. This article provides an improved method to solve this problem.

Assume the data is as follows:

<code>John/Smith
Jane/Doe
Steve
Bob/Johnson</code>

Original code:

The following code attempts to split the data but fails when the delimiter is missing:

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

Improved code:

To handle the case of missing delimiters, we can use the following CASE expression:

<code>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</code>

Description:

• CASE expression checks whether the delimiter (/) exists.
• If present, the expression calculates the length of FirstName and LastName based on the position of the delimiter.
• If not present, the expression will use the entire length of the string as FirstName and set LastName to NULL.

This code will produce the expected result:

<code>FirstName---LastName
John--------Smith
Jane--------Doe
Steve-------NULL
Bob---------Johnson</code>

The above is the detailed content of How to Robustly Split Strings in T-SQL Using Delimiters?. For more information, please follow other related articles on the PHP Chinese website!