在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 abcdewant to convert it to this
Data a b d c eI 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];更多推荐
发布评论