在MS SQL中，如何将列分成没有分隔符的行(In MS SQL how to split a column into rows with no delimiter)

在MS SQL中，如何将列分成没有分隔符的行(In MS SQL how to split a column into rows with no delimiter)

我有一个表格中的数据看起来像这样（值得注意的是它不是CSV分离）

它需要分成单个字符

Data abcde

想把它转换成这个

Data a b d c e

我在网上看过，但还没有找到答案

I have data in a table which looks like this (worth noting its not CSV seperated)

It needs to be split in to single chars

Data abcde

want to convert it to this

Data a b d c e

I have looked on the internet but have not found the answer

最满意答案

CREATE FUNCTION dbo.SplitLetters ( @s NVARCHAR(MAX) ) RETURNS @t TABLE ( [order] INT, [letter] NCHAR(1) ) AS BEGIN DECLARE @i INT; SET @i = 1; WHILE @i <= LEN(@s) BEGIN INSERT @t SELECT @i, SUBSTRING(@s, @i, 1); SET @i = @i + 1; END RETURN; END GO SELECT [letter] FROM dbo.SplitLetters(N'abcdefgh12345 6 7') ORDER BY [order]; CREATE FUNCTION dbo.SplitLetters ( @s NVARCHAR(MAX) ) RETURNS @t TABLE ( [order] INT, [letter] NCHAR(1) ) AS BEGIN DECLARE @i INT; SET @i = 1; WHILE @i <= LEN(@s) BEGIN INSERT @t SELECT @i, SUBSTRING(@s, @i, 1); SET @i = @i + 1; END RETURN; END GO SELECT [letter] FROM dbo.SplitLetters(N'abcdefgh12345 6 7') ORDER BY [order];