1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
| --> la requête suivante :
WITH
T AS
(
SELECT TIP_ID, TIP_ADR AS ORIGINAL_IP,
SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR1,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
FROM TIP
),
TT AS
(SELECT TIP_ID, ORIGINAL_IP, BIP_ADR1, SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS BIP_ADR2,
SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
FROM T)
SELECT TIP_ID, ORIGINAL_IP,
CAST(BIP_ADR1 AS TINYINT) AS BIP_ADR1,
CAST(BIP_ADR2 AS TINYINT) AS BIP_ADR2,
CAST(SUBSTRING(TIP_ADR, 1, CHARINDEX('.', TIP_ADR) - 1) AS TINYINT) AS BIP_ADR3,
CAST(SUBSTRING(TIP_ADR, CHARINDEX('.', TIP_ADR) + 1, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TINYINT) AS BIP_ADR4
FROM TT
ORDER BY 3, 4, 5, 6 |
Partager