sql - String_Split 表的值

Question

我有一个包含以下数据的表:

SequenceID Values
____________________________________
1   111111111111111111111100000111110000
2   111111111111000000000000011110000001
3   111111111110000000000000000000000011
4   111111111100000111111000000000000111
5   111111111000000011111111100000001111
6   111111110000000000111111110000011111
7   111111100000000111111111000000111111
8   111111000000001111111000000000111111
9   111110000000011111100000000000111111
10  111100000000000000000000000000111111

检索只有 0 或 1 的序列的最佳方法是什么。例如，如果我只想检索零，我会得到类似的东西:

SequenceID  Zeros
_________________
1           5
1           4
2           13
2           6
3           23
ETC

OR

SequenceID  Zeros
_________________
1           '00000'
1           '0000'
2           '0000000000000'
2           '000000'
3           '00000000000000000000000'
ETC

我试图使用 STRING_SPLIT 来实现这一点，但它似乎不正确，我无法对表中存在的值执行它。

Answer 1

您可以使用 PatternSplitCM 轻松解决此类问题.

-- Sample Data
DECLARE @table TABLE (SequenceID INT, [Values] CHAR(36));
INSERT @table VALUES
(1 ,  111111111111111111111100000111110000),
(2 ,  111111111111000000000000011110000001),
(3 ,  111111111110000000000000000000000011),
(4 ,  111111111100000111111000000000000111),
(5 ,  111111111000000011111111100000001111),
(6 ,  111111110000000000111111110000011111),
(7 ,  111111100000000111111111000000111111),
(8 ,  111111000000001111111000000000111111),
(9 ,  111110000000011111100000000000111111),
(10,  111100000000000000000000000000111111);

-- solution
SELECT t.SequenceID, zeros = LEN(s.Item) 
FROM   @table AS t
CROSS APPLY dbo.PatternSplitCM(t.[Values],'0') AS s
WHERE  s.[Matched] = 1;

结果:

SequenceID  zeros
----------- -----------
1           5
1           4
2           13
2           6
3           23
4           5
4           12
5           7
5           7
6           10
6           5
7           8
7           6
8           8
8           9
9           8
9           11
10          26