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

Question

我有一些数据，想要根据可能存在的分隔符进行拆分。

示例数据：

John/Smith
Jane/Doe
Steve
Bob/Johnson

我正在使用以下代码将此数据拆分为名字和姓氏：

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

只要所有行都有预期的分隔符，这段代码就能正常工作，但是当一行没有分隔符时，就会出错：

"Invalid length parameter passed to the LEFT or SUBSTRING function."

我该如何重新编写代码以使其正常工作？

- Sesame

Answer 1

也许这会对你有所帮助。

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

- sureshhh

Answer 2

对于那些寻找 SQL Server 2016+ 答案的人，可以使用内置的 STRING_SPLIT 函数。

例如：

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'  

SELECT value  
FROM STRING_SPLIT(@tags, ',')  
WHERE RTRIM(value) <> '';  

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

- raga

---

我不确定这是否回答了问题，也不如上面的CASE语句方法有用。STRING_SPLIT函数将其转换为一个表格，需要联接来获取第一列“名字”和第二列“姓氏”？使用CROSS APPLY会将ds转换为多行。这是一种很好的将字符串数据转换为表格的方法。像OP一样，我正在尝试将单个列转换为两个列。STRING_SPLIT将其转换为两行，并且无法知道哪一行是“第一”，“第二”，“第三”（除非使用OVER）。但那会变得更加复杂。 - ripvlan

Answer 3

尝试过滤掉包含分隔符的字符串所在的行，仅处理这些行，例如：

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 '%/%'

- jpw

Answer 4

从MyTable表中选择一个名为myColumn的列，如果该列中不包含“/”则返回原始值作为FirstName，否则仅返回第一个“/”之前的部分作为FirstName；同理，返回LastName时仅返回最后一个“/”之后的部分作为LastName。

- chaitanya.moguluri

Answer 5

ALTER FUNCTION [dbo].[split_string](
          @delimited NVARCHAR(MAX),
          @delimiter NVARCHAR(100)
        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
  DECLARE @xml XML
  SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

  INSERT INTO @t(val)
  SELECT  r.value('.','varchar(MAX)') as item
  FROM  @xml.nodes('/t') as records(r)
  RETURN
END

- Bruno

Answer 6

如果你使用的是 SQL Server 2016 版本以下的版本，我想提供一种用多个分隔符拆分字符串的替代方法。

总体思路是将字符串中的所有字符拆分出来，确定分隔符的位置，然后获取相对于分隔符的子字符串。以下是一个示例：

Original Answer 翻译成“最初的回答”

-- Sample data
DECLARE @testTable TABLE (
    TestString      VARCHAR(50)
)
INSERT INTO @testTable VALUES 
    ('Teststring,1,2,3')
    ,('Test')

DECLARE @delimiter VARCHAR(1) = ','

-- Generate numbers with which we can enumerate
;WITH Numbers AS (
    SELECT 1 AS N

    UNION ALL 

    SELECT N + 1
    FROM Numbers 
    WHERE N < 255
), 
-- Enumerate letters in the string and select only the delimiters
Letters AS (
    SELECT  n.N
            , SUBSTRING(t.TestString, n.N, 1) AS Letter
            , t.TestString 
            , ROW_NUMBER() OVER (   PARTITION BY t.TestString
                                    ORDER BY n.N
                                ) AS Delimiter_Number 
    FROM Numbers n
        INNER JOIN @testTable t
            ON n <= LEN(t.TestString)
    WHERE SUBSTRING(t.TestString, n, 1) = @delimiter 

    UNION 

    -- Include 0th position to "delimit" the start of the string
    SELECT  0
            , NULL
            , t.TestString 
            , 0
    FROM @testTable t 
)
-- Obtain substrings based on delimiter positions
SELECT  t.TestString 
        , ds.Delimiter_Number + 1 AS Position
        , SUBSTRING(t.TestString, ds.N + 1, ISNULL(de.N, LEN(t.TestString) + 1) - ds.N - 1) AS Delimited_Substring 
FROM @testTable t
    LEFT JOIN Letters ds
        ON t.TestString = ds.TestString 
    LEFT JOIN Letters de
        ON t.TestString = de.TestString 
        AND ds.Delimiter_Number + 1 = de.Delimiter_Number  
OPTION (MAXRECURSION 0)

- Michael Kwon

Answer 7

当只有一个分隔符时，上面的示例可以正常工作，但对于多个分隔符来说不够灵活。请注意，这仅适用于 SQL Server 2016 及以上版本。

/*Some Sample Data*/
DECLARE @mytable TABLE ([id] VARCHAR(10), [name] VARCHAR(1000));
INSERT INTO @mytable
VALUES ('1','John/Smith'),('2','Jane/Doe'), ('3','Steve'), ('4','Bob/Johnson')


/*Split based on delimeter*/
SELECT P.id, [1] 'FirstName', [2] 'LastName', [3] 'Col3', [4] 'Col4'
FROM(
    SELECT A.id, X1.VALUE, ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY A.id) RN
    FROM @mytable A
    CROSS APPLY STRING_SPLIT(A.name, '/') X1
    ) A
PIVOT (MAX(A.[VALUE]) FOR A.RN IN ([1],[2],[3],[4],[5])) P

- James Moore

Answer 8

我会通过在测试字符串中始终附加分隔符来保护子字符串操作。这使得解析变得更简单。现在，您的代码可能依赖于找到正确的模式，而不需要处理特殊情况。

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

它消除了边缘情况和条件语句。 始终在末尾添加额外的分隔符，那么挑战案例就不是问题。

- silvertc

Answer 9

这些都帮助我达到了这个目标。虽然我仍在使用2012版本，但现在我有了一个快速的方法，可以分割一个字符串，即使该字符串具有不同数量的分隔符，并从该字符串中获取第n个子字符串。它也很快。我知道这篇文章已经过时了，但我花了很长时间才找到这个方法，希望能帮助其他人。

CREATE FUNCTION [dbo].[SplitsByIndex]
(@separator VARCHAR(20)  = ' ', 
 @string    VARCHAR(MAX), 
 @position  INT
)
RETURNS VARCHAR(MAX)
AS
     BEGIN
     DECLARE @results TABLE
     (id   INT IDENTITY(1, 1), 
      chrs VARCHAR(8000)
     );
     DECLARE @outResult VARCHAR(8000);
     WITH X(N)
          AS (SELECT 'Table1'
              FROM(VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) T(C)),
          Y(N)
          AS (SELECT 'Table2'
              FROM X A1, 
                   X A2, 
                   X A3, 
                   X A4, 
                   X A5, 
                   X A6, 
                   X A7, 
                   X A8), -- Up to 16^8 = 4 billion
          T(N)
          AS (SELECT TOP (ISNULL(LEN(@string), 0)) ROW_NUMBER() OVER(
                                                   ORDER BY
              (
                  SELECT NULL
              )) - 1 N
              FROM Y),
          Delim(Pos)
          AS (SELECT t.N
              FROM T
              WHERE(SUBSTRING(@string, t.N, LEN(@separator + 'x') - 1) LIKE @separator
                    OR t.N = 0)),
          Separated(value)
          AS (SELECT SUBSTRING(@string, d.Pos + LEN(@separator + 'x') - 1, LEAD(d.Pos, 1, 2147483647) OVER(
                     ORDER BY
              (
                  SELECT NULL
              ))-d.Pos - LEN(@separator))
              FROM Delim d
              WHERE @string IS NOT NULL)
          INSERT INTO @results(chrs)
                 SELECT s.value
                 FROM Separated s
                 WHERE s.value <> @separator;
     SELECT @outResult =
     (
         SELECT chrs
         FROM @results
         WHERE id = @position
     );
     RETURN @outResult;
 END;

这可以这样使用：

SELECT [dbo].[SplitsByIndex](' ',fieldname,2) 
from tablename

- codingWannabe