将不带分隔符的字符串拆分为列 [英] Split a string with no delimiters into columns

问题描述

我需要在SQL Server 2012中将列中的字符串拆分为一个字符，每个字符串都在自己的列中。

I need to split a string in a column into one character each into it's own column in SQL Server 2012.

示例：如果我有一个'ABCDE'，我需要将它拆分为'A'，'B'，'C'，'D'，'E' code>，其中每一个都在自己的列中。

Example: if I have a column with 'ABCDE', I need to split it into 'A', 'B', 'C', 'D', 'E', with each of these into their own columns.

要分割的列的长度可能会有所不同，因此我需要可能。

The length of the column to be split may vary, so I need this to be as dynamic as possible.

我的问题与其他帖子不同（可以Mysql拆分列吗？），因为我没有任何分隔符。
感谢

My question is different from the other post (Can Mysql Split a column?) since mine doesn't have any delimiters. Thanks

推荐答案

您可以这样做：

DECLARE @t TABLE(id int, n VARCHAR(50))
INSERT INTO @t VALUES
(1, 'ABCDEF'),
(2, 'EFGHIJKLMNOPQ')


;WITH cte AS
(SELECT id, n, SUBSTRING(n, 1, 1) c, 1 AS ind FROM @t
 UNION ALL 
 SELECT id, n, SUBSTRING(n, ind + 1, 1), ind + 1 FROM cte WHERE LEN(n) > ind
)

SELECT *
FROM cte 
PIVOT (MAX(c) FOR ind IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[12],[13],[14],[15])) p

输出：

id  n               1   2   3   4   5   6   7    8    9    10   12   13   14    15
1   ABCDEF          A   B   C   D   E   F   NULL NULL NULL NULL NULL NULL NULL  NULL
2   EFGHIJKLMNOPQ   E   F   G   H   I   J   K    L    M    N    P    Q    NULL  NULL

这是动态版本：

DECLARE @l INT, @c VARCHAR(MAX) = ''
SELECT @l = MAX(LEN(n)) FROM PivotTable

WHILE @l > 0
BEGIN
 SET @c = ',[' + CAST(@l AS VARCHAR(MAX)) + ']' + @c
 SET @l = @l - 1
END

SET @c = STUFF(@c, 1, 1,'')

DECLARE @s NVARCHAR(MAX) = '
;WITH cte AS
(SELECT id, n, SUBSTRING(n, 1, 1) c, 1 AS ind FROM PivotTable
 UNION ALL 
 SELECT id, n, SUBSTRING(n, ind + 1, 1), ind + 1 FROM cte WHERE LEN(n) > ind
)

SELECT *
FROM cte 
PIVOT (MAX(c) FOR ind IN(' + @c + ')) p'

EXEC (@s)

这篇关于将不带分隔符的字符串拆分为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！