Segment string based on delimiter in T-SQL

question:

In SQL, using SUBSTRING to split strings based on delimiters can cause errors when the delimiter does not exist. The following code demonstrates this:

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

When a line without a delimiter is encountered, it throws an error: "The length parameter 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 shown below:

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

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 exists, it calculates the position of the first character after the delimiter as the starting index of the last name.