Friday, June 8, 2012

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

No comments:

Post a Comment

Dear User,

Thank you very much for your kind response