1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| CREATE FUNCTION FnDecortique (@str VARCHAR(20))
RETURNS TABLE
AS RETURN
(
WITH CTE (Deb, Fin) AS
(
SELECT 1 Deb, CHARINDEX('|', REPLACE(REPLACE(REPLACE(REPLACE(@str, '-', '|'), '+', '|'), '*', '|'), '/', '|') + '|') Fin
UNION ALL
SELECT Fin + 1, CHARINDEX('|', REPLACE(REPLACE(REPLACE(REPLACE(@str, '-', '|'), '+', '|'), '*', '|'), '/', '|') + '|', Fin + 1)
FROM CTE
WHERE CHARINDEX('|', REPLACE(REPLACE(REPLACE(REPLACE(@str, '-', '|'), '+', '|'), '*', '|'), '/', '|') + '|', Fin + 1 ) > 0
)
SELECT SUBSTRING(@str, Deb , Fin - Deb) Extrait
FROM CTE
WHERE Deb <> Fin
AND ISNUMERIC(SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(@str, '-', '|'), '+', '|'), '*', '|'), '/', '|') + '|', Deb , Fin - Deb)) = 1
OR LEN(SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(@str, '-', '|'), '+', '|'), '*', '|'), '/', '|') + '|', Deb , Fin - Deb)) > 1
) |
Partager