This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input and require output, and convert single cell of a table into multiple Rows.
Input:
|
1 2 3 |
string ---------- aBCDe14885 |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
string ------ a B C D e 1 4 8 8 5 |
Solution 1:
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @Var VARCHAR(10) = 'aBCDe14885' GO ;with cte as ( select 1 as rnk, substring(@Var,1,1) as string union all select rnk +1 as rnk, substring(@Var,rnk+1,1) as string from cte where rnk < datalength(@Var) ) select string from cte GO |
Solution 2:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @Var VARCHAR(10) = 'aBCDe14885' GO ;with cte as ( select substring(@Var, 1, 1) as string ,substring(@Var, 2, len(@Var)) as data ,1 as rnk union all select substring(data, 1, 1) as string ,substring(data, 2, len(data)) as data ,rnk + 1 as rnk from cte where len(data) > 0 ) select string from cte GO |
Please try the different solution for this puzzle and share it via comment...
