Sesame Sesame - 5 months ago 34
SQL Question

T-SQL split string based on delimiter

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 a re-write this to work properly?

Answer

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
Comments