Geek Answers Handbook

In MS SQL, how to split a column into rows without a delimiter

I have data in a table that looks like this (it is worth noting that it is not divided by CSV)

It must be divided into separate characters

Data abcde

want to convert it to this

 Data a b d c e

I looked on the Internet but could not find an answer

+4
source share
5 answers
 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];
+6
source

Previous post that solves the problem: TSQL UDF for line splitting Every 8 characters

Pass a value from 1 to @length.

+3
source 
 declare @T table ( ID int identity, Data varchar(10) ) insert into @T select 'ABCDE' union select '12345' ;with cte as ( select ID, left(Data, 1) as Data, stuff(Data, 1, 1, '') as Rest from @T where len(Data) > 0 union all select ID, left(Rest, 1) as Data, stuff(Rest, 1, 1, '') as Rest from cte where len(Rest) > 0 ) select ID, Data from cte order by ID
+3
source

You can join the table with a list of numbers and use substring to split the data column into rows:

 declare @YourTable table (data varchar(50)) insert @YourTable select 'abcde' union all select 'fghe' ; with nrs as ( select max(len(data)) as i from @YourTable union all select i - 1 from nrs where i > 1 ) select substring(yt.data, i, 1) from nrs join @YourTable yt on nrs.i < len(yt.data) option (maxrecursion 0)
+3
source 
 declare @input varchar(max); set @input = 'abcde' declare @table TABLE (char varchar(1)); while (LEN(@input)> 0) begin insert into @table select substring(@input,1,1) select @input = RIGHT(@input,Len(@input)-1) end select * from @table
+2
source

More articles:


All Articles