将字符串拆分为两列
将字符串拆分为两列
提问于 2019-09-18 18:29:58
我将以下字符串拆分为两列：
给定：
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回答 5
Stack Overflow用户
回答已采纳
发布于 2019-09-18 18:41:22
首先，请注意SQL Server2008 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 5Stack Overflow用户
发布于 2019-09-18 18:41:49
尝试下面的脚本
DECLARE @String VARCHAR(MAX) = 'Mak^1,Jak^2,Smith^3,Lee^4,Joseph^5';
DECLARE @TempTable AS TABLE(data VARCHAR(MAX))
INSERT INTO @TempTable
SELECT @String
;WITH CTE
AS
(
SELECT Split.A.value('.','nvarchar(1000)') AS data
FROM
(
SELECT CAST('<S>'+REPLACE(data,',','</S><S>')+'</S>' AS XML ) AS data
FROM @TempTable
)AS A
CROSS APPLY data.nodes('S') AS Split(A)
)
SELECT LTRIM(RTRIM(SUBSTRING(data,0,CHARINDEX('^',data)))) AS column1,
LTRIM(RTRIM(SUBSTRING(data,CHARINDEX('^',data)+1,LEN (data)))) AS column2
FROM CTE结果
column1 column2
-------------------
Mak 1
Jak 2
Smith 3
Lee 4
Joseph 5使用上面的脚本创建表值参数函数
CREATE FUNCTION [dbo].[udf_SplitFun](@InputData VARCHAR(MAX))
RETURNS @Return TABLE ( column1 VARCHAR(200),column2 INT)
AS
BEGIN
DECLARE @TempTable AS TABLE
(
data VARCHAR(MAX)
)
INSERT INTO @TempTable
SELECT @InputData
;WITH CTE
AS
(
SELECT Split.A.value('.','nvarchar(1000)') AS data
FROM
(
SELECT CAST('<S>'+REPLACE(data,',','</S><S>')+'</S>' AS XML ) AS data
FROM @TempTable
)AS A
CROSS APPLY data.nodes('S') AS Split(A)
)
INSERT INTO @Return(column1,column2)
SELECT LTRIM(RTRIM(SUBSTRING(data,0,CHARINDEX('^',data)))) AS column1,
LTRIM(RTRIM(SUBSTRING(data,CHARINDEX('^',data)+1,LEN (data)))) AS column2
FROM CTE
RETURN;
END如下所示执行函数
DECLARE @InputData VARCHAR(MAX) = 'Mak^1,Jak^2,Smith^3,Lee^4,Joseph^5';
SELECT * FROM [dbo].[udf_SplitFun] (@InputData)
GOStack Overflow用户
发布于 2019-09-18 18:42:52
我觉得解决这个问题的更好的方法是去掉糟糕的WHILE，使用基于集合的方法；这里我们将使用delimitedsplit8K (如果您在2012+上使用delimitedsplit8k_lead，或者在2016+上可以使用STRING_SPLIT)。
考虑到这一点，上面的内容就变得相当微不足道：
DECLARE @String varchar(MAX) = 'Mak^1,Jak^2,Smith^3,Lee^4,Joseph^5';
SELECT LEFT(DS.Item,CHARINDEX('^',DS.Item)-1) AS Col1,
STUFF(DS.Item,1, CHARINDEX('^',DS.Item),'') AS Col2
FROM dbo.DelimitedSplit8K(@String, ',') DS;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57990677
复制相关文章
相似问题
腾讯云开发者
