I am trying to split a string in SQL with the following format: 'John, Mark, Peter|23, 32, 45'.
我试图在SQL中使用以下格式拆分字符串:'John,Mark,Peter | 23,32,45'。
The idea is to have all the names in the first columns and the ages in the second column.
我们的想法是在第一列中包含所有名称,在第二列中包含年龄。
The query should be "dynamic", the string can have several records depending on user entries.
查询应该是“动态的”,字符串可以有多个记录,具体取决于用户条目。
Does anyone know how to this, and if possible without SQL functions? I have tried the cross apply approach but I wasn't able to make it work.
有没有人知道如何,如果可能没有SQL函数?我尝试过交叉应用方法,但我无法使其工作。
Any ideas?
1 个解决方案
#1
1
This solution uses Jeff Moden's DelimitedSplit8k. Why? Because his solution provides the ordinal position of the item. Ordinal Position something that many others functions, including Microsoft's own STRING_SPLIT, does not provide. It's going to be vitally important for getting this to work correctly.
该解决方案使用Jeff Moden的DelimitedSplit8k。为什么?因为他的解决方案提供了项目的序数位置。序数位置许多其他功能,包括微软自己的STRING_SPLIT,没有提供。让它正常工作将是至关重要的。
Once you have that, the solution becomes fairly simple:
一旦你有了,解决方案变得相当简单:
DECLARE @NameAges varchar(8000) = 'John, Mark, Peter|23, 32, 45';
WITH Splits AS (
SELECT S1.ItemNumber AS PipeNumber,
S2.ItemNumber AS CommaNumber,
S2.Item
FROM dbo.DelimitedSplit8K (REPLACE(@NameAges,' ',''), '|') S1 --As you have spaces between the delimiters I've removed these. Be CAREFUL with that
CROSS APPLY DelimitedSplit8K (S1.item, ',') S2)
SELECT S1.Item AS [Name],
S2.Item AS Age
FROM Splits S1
JOIN Splits S2 ON S1.CommaNumber = S2.CommaNumber
AND S2.PipeNumber = 2
WHERE S1.PipeNumber = 1;
#1
1
This solution uses Jeff Moden's DelimitedSplit8k. Why? Because his solution provides the ordinal position of the item. Ordinal Position something that many others functions, including Microsoft's own STRING_SPLIT, does not provide. It's going to be vitally important for getting this to work correctly.
该解决方案使用Jeff Moden的DelimitedSplit8k。为什么?因为他的解决方案提供了项目的序数位置。序数位置许多其他功能,包括微软自己的STRING_SPLIT,没有提供。让它正常工作将是至关重要的。
Once you have that, the solution becomes fairly simple:
一旦你有了,解决方案变得相当简单:
DECLARE @NameAges varchar(8000) = 'John, Mark, Peter|23, 32, 45';
WITH Splits AS (
SELECT S1.ItemNumber AS PipeNumber,
S2.ItemNumber AS CommaNumber,
S2.Item
FROM dbo.DelimitedSplit8K (REPLACE(@NameAges,' ',''), '|') S1 --As you have spaces between the delimiters I've removed these. Be CAREFUL with that
CROSS APPLY DelimitedSplit8K (S1.item, ',') S2)
SELECT S1.Item AS [Name],
S2.Item AS Age
FROM Splits S1
JOIN Splits S2 ON S1.CommaNumber = S2.CommaNumber
AND S2.PipeNumber = 2
WHERE S1.PipeNumber = 1;