1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| WITH
split_prenom AS
(SELECT nom, CASE INSTR(prenoms, ',') = 0 THEN prenoms ELSE SUBSTR(prenoms, 1, INSTR(prenoms, ',') - 1) END AS prenom,
CASE INSTR(prenoms, ',') = 0 THEN '' ELSE SUBSTR(prenoms, INSTR(prenoms, ',') + 1, LENGTH(prenoms) - INSTR(prenoms, ',')) END AS prenoms
FROM recherche
UNION ALL
SELECT nom, CASE INSTR(prenoms, ',') = 0 THEN prenoms ELSE SUBSTR(prenoms, 1, INSTR(prenoms, ',') - 1) END AS prenom,
CASE INSTR(prenoms, ',') = 0 THEN '' ELSE SUBSTR(prenoms, INSTR(prenoms, ',') + 1, LENGTH(prenoms) - INSTR(prenoms, ',')) END AS prenoms
FROM split_prenom
WHERE prenoms <> ''
)
SELECT *
FROM CLIENTS AS C
JOIN split_prenom AS R
ON C.nom = R.nom AND C.prenom = R.prenom |
Partager