sql - 将字符串拆分为两列

Question

我将以下字符串分成两列:

给定:

DECLARE @String VARCHAR(MAX) = 'Mak^1,Jak^2,Smith^3,Lee^4,Joseph^5'

我想把它分成两列:

column1  column2
-----------------
Mak      1
Jak      2
Smith    3
Lee      4
Joseph   5

我的尝试:

表值函数:

CREATE FUNCTION [dbo].[udf_Split]
(
@InputString VARCHAR(8000), 
@Delimiter VARCHAR(50)
)
RETURNS @Items TABLE (ID INTEGER IDENTITY(1,1), Item VARCHAR(8000))

AS
BEGIN
      IF @Delimiter = ' '
      BEGIN
            SET @Delimiter = ','
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
      END
      IF (@Delimiter IS NULL OR @Delimiter = '')
            SET @Delimiter = ','

      DECLARE @Item VARCHAR(8000)
      DECLARE @ItemList VARCHAR(8000)
      DECLARE @DelimIndex INT

      SET @ItemList = @InputString
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      WHILE (@DelimIndex != 0)
      BEGIN
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
            INSERT INTO @Items VALUES (@Item)

            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      END -- End WHILE

      IF @Item IS NOT NULL 
      BEGIN
            SET @Item = @ItemList
            INSERT INTO @Items VALUES (@Item)
      END

      ELSE INSERT INTO @Items VALUES (@InputString)

      RETURN

END 

函数调用:

SELECT Item FROM [dbo].[udf_Split](@String ,',');

输出:

Item
--------------
Mak^1
Jak^2
Smith^3
Lee^4
Joseph^5

Answer 1

首先，请注意 SQL Server 2008 r2 不在扩展支持范围内。是时候升级到更新的版本了。

对于单个字符串，我可能会使用一些动态 SQL 魔术:

DECLARE @String VARCHAR(MAX) = 'Mak^1,Jak^2,Smith^3,Lee^4,Joseph^5'

DECLARE @Sql VARCHAR(MAX) = 'SELECT Name,Id FROM (VALUES (''' + REPLACE(REPLACE(REPLACE(@String,'''',''''''), ',', '),('''), '^', ''',') + ')) V(Name, Id)';

-- @Sql now contains this:
-- SELECT Name,Id FROM (VALUES ('Mak',1),('Jak',2),('Smith',3),('Lee',4),('Joseph',5)) V(Name, Id)

EXEC(@Sql)

结果:

Name    Id
Mak     1
Jak     2
Smith   3
Lee     4
Joseph  5