基于分隔符的 T-SQL 拆分字符串 [英] 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.

示例数据:

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

我想要的结果:

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?

推荐答案

也许这对你有帮助.

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

这篇关于基于分隔符的 T-SQL 拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！