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
| declare @mytable table (co_orga varchar(100));
insert into @mytable (co_orga) values
('DGS / DGA_C / DEJS (Direction Education Jeunesse et Sports)'),
('DGS / DGA_C / DEJS / Education / Collège'),
('DGS / DF (Direction des Finances)'),
('DGS / DGA_T / DID / DAAT / SR Plaine-Littoral'),
('DGS / DGA_T / DEE / SN (Service Sites Naturels)');
select co_orga
, case
when charindex('(', co_orga) > 0 and charindex(')', co_orga) > charindex('(', co_orga)
then substring(co_orga, charindex('(', co_orga) + 1, charindex(')', co_orga) - charindex('(', co_orga) - 1)
else substring(co_orga, len(co_orga) - charindex('/', reverse(co_orga)) + 3, len(co_orga))
end as libelle
, case
when charindex('(', co_orga) > 0 and charindex(')', co_orga) > charindex('(', co_orga)
then substring(co_orga, 1, charindex('(', co_orga) - 2)
else co_orga
end as id_court
, substring(co_orga, 1, len(co_orga) - charindex('/', reverse(co_orga)) - 1) as id_parent
from @mytable;
co_orga libelle id_court id_parent
----------------------------------------------------------- -------------------------------------- --------------------------------------------- ------------------------------
DGS / DGA_C / DEJS (Direction Education Jeunesse et Sports) Direction Education Jeunesse et Sports DGS / DGA_C / DEJS DGS / DGA_C
DGS / DGA_C / DEJS / Education / Collège Collège DGS / DGA_C / DEJS / Education / Collège DGS / DGA_C / DEJS / Education
DGS / DF (Direction des Finances) Direction des Finances DGS / DF DGS
DGS / DGA_T / DID / DAAT / SR Plaine-Littoral SR Plaine-Littoral DGS / DGA_T / DID / DAAT / SR Plaine-Littoral DGS / DGA_T / DID / DAAT
DGS / DGA_T / DEE / SN (Service Sites Naturels) Service Sites Naturels DGS / DGA_T / DEE / SN DGS / DGA_T / DEE |
Partager