1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
| create table matable (id_ligne serial, valeur varchar(30));
insert into matable (valeur) values ('ABCD'), ('ABXY');
with RECURSIVE cter_matable (id_ligne, valeur, extract, pos) as
(
select id_ligne
, valeur
, substring(valeur, 1, 2)
, cast(1 as int)
from matable
union all
select id_ligne
, valeur
, substring(valeur, pos + 1, 2)
, pos + 1
from cter_matable
where length(substring(valeur, pos + 1, 2)) = 2
)
select extract
from cter_matable
group by extract
order by min(id_ligne), min(pos);
EXTRACT
-------
AB
BC
CD
BX
XY |
Partager