基于分隔符的T-SQL分割字符串

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?

如何重新写这个以正常工作?

4 个解决方案

#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

#2

SELECT CASE 
        WHEN CHARINDEX('/', myColumn, 0) = 0
            THEN myColumn
        ELSE LEFT(myColumn, CHARINDEX('/', myColumn, 0)-1)
        END AS FirstName
    ,CASE 
        WHEN CHARINDEX('/', myColumn, 0) = 0
            THEN ''
        ELSE RIGHT(myColumn, CHARINDEX('/', REVERSE(myColumn), 0)-1)
        END AS LastName
FROM MyTable

#3

For those looking for answers fore SQL Server 2016. Use String_Split function

对于那些寻找SQL Server 2016答案的人来说。使用String_Split函数

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

参考:https://msdn.microsoft.com/en-nz/library/mt684588.aspx

#4

Try filtering out the rows that contain strings with the delimiter and work on those only like:

尝试过滤包含带分隔符的字符串的行，只处理以下这些行:

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

或

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

#1