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?

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 

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