Display Sring in vertical manner

Question

I have String like as follows

Declare @string ='welcome'

and i want output like this

w
e
l
c
o
m
e

What kind of output? A debug print or a string that is returned? Or smth else? — TomBombadil, Aug 31 '18 at 07:38

Grenther · Answer 1 · 2018-08-31T07:47:59.953

0

DECLARE @string VARCHAR(256) = 'welcome'
DECLARE @cnt INT = 0;

WHILE @cnt < len(@string)
BEGIN
    SET @cnt = @cnt + 1;
    PRINT SUBSTRING ( @string ,@cnt , 1 )  
END;

In essence you loop through the length of the string. You print the character at the location of the index of the loop and print that.

edited Aug 31 '18 at 07:47

answered Aug 31 '18 at 07:40

Grenther

476
3
10

Yes it work fine and thank u – Krishna M Aug 31 '18 at 07:54
I have array of sting as 'chennai,mumbai,Bangalore,Hyderabad,pune' --and i want to output like this chennai mumbai Bangalore Hyderabad Pune what is the logic for this one bro – Krishna M Aug 31 '18 at 07:55
@KrishnaM I think you should refer to following link: https://stackoverflow.com/questions/8517816/t-sql-split-word-into-characters – MrRaghav Jan 26 '23 at 09:53

score 0 · Answer 2 · answered Aug 31 '18 at 07:44

You can use a tally table for this, usually faster than looping, but you would have to test for yourself:

declare @s varchar(200)

set     @s = 'Welcome'


;with t as (
        select  v
        from    (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(v)
),
n as (
        select  row_number() over(order by (select null)) rn
        from    t t1 cross join t t2
)
select  substring(@s, rn, 1)
from    n
where   rn <= len(@s)

score 0 · Answer 3 · answered Aug 31 '18 at 07:55

You ca use recursive CTE.

Declare @string varchar(10) ='welcome'
;with cte as 
(
select 1 as i,substring(@string,1,1) as single_char
union all
select i+1 as i,convert(varchar(1),substring(@string,i+1,i+1)) as single_char from cte where len(convert(varchar(1),substring(@string,i+1,i+1)))=1
)
select single_char From cte

Display Sring in vertical manner

3 Answers3