1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
|
WITH CTE1 AS
(SELECT '445A' AS TXT
UNION
SELECT 'XX 56B/V44/' AS TXT
UNION
SELECT 'XX 56B/V44/123X5_C/' AS TXT)
SELECT
TXT
,CASE WHEN LOCATE_IN_STRING(TXT, '/', 1, 1) > 0
THEN SUBSTR(TXT, 1, LOCATE_IN_STRING(TXT, '/', 1, 1)-1)
ELSE TXT
END AS POS1
,CASE WHEN LOCATE_IN_STRING(TXT, '/', 1, 2) > 0
THEN SUBSTR(TXT, LOCATE_IN_STRING(TXT, '/', 1, 1)+1
,(LOCATE_IN_STRING(TXT, '/', 1, 2)
-LOCATE_IN_STRING(TXT, '/', 1, 1) - 1))
END AS POS2
,CASE WHEN LOCATE_IN_STRING(TXT, '/', 1, 3) > 0
THEN SUBSTR(TXT, LOCATE_IN_STRING(TXT, '/', 1, 2)+1
,(LOCATE_IN_STRING(TXT, '/', 1, 3)
-LOCATE_IN_STRING(TXT, '/', 1, 2) - 1))
END AS POS3
FROM CTE1 |
Partager