Microsoft SQL Server 2005:Change string value into multiple rows

Sample Table based on select query.

select 1 r,'abcd' String
union all
select 2,'efgh'
union all
select 3,'ijkl'
union all
select 4,'mnop'
union all
select 5,'qrst'
union all
select 6,'uvwx'
union all
select 7,'yz'

Create #temp table.

select * into #temp from (

select 1 r,'abcd' String
union all
select 2,'efgh'
union all
select 3,'ijkl'
union all
select 4,'mnop'
union all
select 5,'qrst'
union all
select 6,'uvwx'
union all
select 7,'yz') a

select *from #temp

Output

r    String

1    abcd

2    efgh

3    ijkl

4    mnop

5    qrst

6    uvwx

7    yz

;WITH

L0 AS (SELECT 1 AS c UNION ALL SELECT 1),

L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),

L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),

L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),

L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),

Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L4),

FilteredNums AS (SELECT i FROM Nums WHERE i<= 400),

Letters AS(

SELECT r,UPPER(SUBSTRING(string,i,1)) AS String

FROM (SELECT *  FROM   #temp

) a

JOIN FilteredNums ON FilteredNums.i <= LEN(string)

)

SELECT * FROM LETTERS

order by 2

Output :

1    A
1    B
1    C
1    D
2    E
2    F
2    G
2    H
3    I
3    J
3    K
3    L
4    M
4    N
4    O
4    P
5    Q
5    R
5    S
5    T
6    U
6    V
6    W
6    X
7    Y
7    Z

Alternatively....

Use this Function....


   
CREATE FUNCTION [dbo].[fnSplitChar]( 
@String varchar(8000)-- String to split all character 
)        
returns @temptable TABLE (items varchar(8000))        
as        
begin        
    declare @idx int        
    declare @slice varchar(8000)        
 
    select @idx = 1        
        if len(@String)<1 or @String is null  return        
 
    while @idx!= 0        
    begin        
        set @idx = 1        
        if @idx!=0        
            set @slice = left(@String,@idx )        
        else        
            set @slice = @String        
 
        if(len(@slice)>0)   
            insert into @temptable(Items) values(@slice)        
 
        set @String = right(@String,len(@String) - @idx)        
        if len(@String) = 0 break        
    end    
return        
end  

E.g.

Select *from dbo.FnSplitChar('12345')

Go


1
2
3
4
5