By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create table tmp1
(
ID bigint,
Code varchar(10)
)
insert into tmp1 values(1,'')
insert into tmp1 values(1,'DCBA')
insert into tmp1 values(1,'BACD')
insert into tmp1 values(1,'ABCD')
select * from tmp1
| ID | Code |
|---|---|
| 1 | |
| 1 | DCBA |
| 1 | BACD |
| 1 | ABCD |
with cte as (
select id, code, stuff(code, 1, 1, '') as rest, left(code, 1) as letter, 1 as lev
from tmp1
union all
select id, code, stuff(rest, 1, 1, '') as rest, left(rest, 1) as letter, lev + 1
from cte
where rest <> ''
)
select id, code, letter, lev
from cte;
| id | code | letter | lev |
|---|---|---|---|
| 1 | 1 | ||
| 1 | DCBA | D | 1 |
| 1 | BACD | B | 1 |
| 1 | ABCD | A | 1 |
| 1 | ABCD | B | 2 |
| 1 | ABCD | C | 3 |
| 1 | ABCD | D | 4 |
| 1 | BACD | A | 2 |
| 1 | BACD | C | 3 |
| 1 | BACD | D | 4 |
| 1 | DCBA | C | 2 |
| 1 | DCBA | B | 3 |
| 1 | DCBA | A | 4 |
with cte as (
select id, code, left(code, 1) as letter, 1 as lev
from tmp1
union all
select id, code, substring(code, lev, 1) as letter, lev + 1
from cte
where lev < len(code)
)
select id, code, letter, lev
from cte;
| id | code | letter | lev |
|---|---|---|---|
| 1 | 1 | ||
| 1 | DCBA | D | 1 |
| 1 | BACD | B | 1 |
| 1 | ABCD | A | 1 |
| 1 | ABCD | A | 2 |
| 1 | ABCD | B | 3 |
| 1 | ABCD | C | 4 |
| 1 | BACD | B | 2 |
| 1 | BACD | A | 3 |
| 1 | BACD | C | 4 |
| 1 | DCBA | D | 2 |
| 1 | DCBA | C | 3 |
| 1 | DCBA | B | 4 |