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

Split string based on delimiter in T-SQL

Question:

In SQL, using SUBSTRING to split a string based on a delimiter may cause an error when the delimiter does not exist. The code below demonstrates this:

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

When a line without a delimiter is encountered, it throws the error: "The length argument passed to the LEFT or SUBSTRING function is invalid."

Solution:

To solve this problem, you can use the CASE statement in the SUBSTRING function as follows:

<code class="language-sql">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>

This updated code uses CHARINDEX to evaluate the position of the delimiter. If the delimiter is not found (CHARINDEX returns 0), the CASE statement uses the length of the string to ensure that the entire string is treated as a name. If a delimiter is present, it calculates the position of the first character after the delimiter as the starting index of the last name.

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