In MS SQL how to split a column into rows with no delimiter

Question

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

Have you looked at this? http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor — Rick Liddle, Jul 08 '11 at 13:20
@Rick Liddle - I have seen that but its designed to use commas - my data is not CSV — Rob, Jul 08 '11 at 13:25
I would also ask the basic question: How can you, as a human being, determine where the data starts and stops? If you can answer that we would be better able to figure out a plan to tell the software how to do the same thing. Based on the example you provide, all I can surmise is that your data is a list of single characters (which I'm sure isn't the case). — jefflunt, Jul 08 '11 at 13:31
@normalocity so im guessing len would tell us the len of the column so we would know the end - start would be zero - yeh it is a single list of chars but I failed to say its split ever char — Rob, Jul 08 '11 at 13:33

score 6 · Accepted Answer · answered Jul 08 '11 at 13:31

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];

score 3 · Answer 2 · edited May 23 '17 at 11:55

3

Previous post that solves the problem: TSQL UDF To Split String Every 8 Characters

Pass a value of 1 to @length.

edited May 23 '17 at 11:55

Community

1
1

answered Jul 08 '11 at 13:33

Rick Liddle

2,684
19
31

score 3 · Answer 3 · answered Jul 08 '11 at 13:33

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

score 3 · Answer 4 · answered Jul 08 '11 at 13:36

You could join the table to a list of numbers, and use substring to split 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)

score 2 · Answer 5 · edited Jul 08 '11 at 13:39

2

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

edited Jul 08 '11 at 13:39

Mikael Eriksson

136,425
22
210
281

answered Jul 08 '11 at 13:35

Matt Evans

7,113
7
32
64

In MS SQL how to split a column into rows with no delimiter

5 Answers5