跨多行拆分可变长度的分隔字符串 (SQL)

问题描述

我有一个表格，其中一列包含一个可变长度的分隔字符串，例如:

I have a table that contains in one column a variable length delimited string for example:

20,0, 5,,^24,0, 0,,^26,0, 0,,^
281,0,0,,^34,0,2,,^48,0,2,,^44,0,2,,^20,0,10,,^
20,5,5,,^379,1,1,,^26,1,2,,^32,0,1,,^71,0,2,,^

我需要做的是拆分此字符串，以便在新行中返回 ^ 字符后的每个数字.喜欢:

What i need to do is split this string so that each number after the ^ character is returned on a new row. like:

商品编号 商品代码
item1 20
项目 2 ^24
项目 3 ^24
项目 4 ^27
项目 5 ^28
项目 6 ^65
项目 7 ^66
项目 8 ^39
项目 9 ^379
项目 10 ^448
项目 11 ^427

我尝试了各种拆分函数，我可以通过对多列中的值进行子串化，然后使用 unpivot 跨多行返回它们来设法实现我需要的结果，但是此方法无法处理此字符串的可变长度.

I've tried various split functions and I can manage to achieve the result i need by substring'ing the values across multiple columns and then using unpivot to return them across multiple rows however this method doesnt handle teh variable length of this string.

有没有更好的方法的想法?

Any ideas of a better approach?

推荐答案

首先，我只想说，这就是您首先不应在字段中使用逗号分隔数据的原因.没有简单或有效的方法来使用它.

First, let me just say that this is the reason that you shouln't have comma separated data in a field in the first place. There is no easy or efficient way to work with it.

也就是说，您可以使用递归查询来拆分字符串并从中获取数字:

That said, you can use a recursive query to split the string and get the numbers from it:

with split as
(
  select
    item = cast('' as varchar(max)),
    source = cast('20,0, 5,,^24,0, 0,,^26,0, 0,,^281,0, 0,,^34,0, 2,,^48,0, 2,,^44,0, 2,,^20,0, 10,,^20,5, 5,,^379,1, 1,,^26,1, 2,,^32,0, 1,,^71,0, 2,,^' as varchar(max))
  union all
  select
    item = substring(source, 1, charindex(',,', source)),
    source = substring(source, charindex(',,', source) + 2, 10000)
  from split
  where source > ''
)
select substring(item, 1, charindex(',', item) -1)
from split
where item > ''

结果:

20
^24
^26
^281
^34
^48
^44
^20
^20
^379
^26
^32
^71

这篇关于跨多行拆分可变长度的分隔字符串 (SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！