Discussion :

[DedeLaMouche]

Bonjour à tous,

Je m'arrache mes derniers cheveux à chercher une solution pour trier le contenu d'une table sur une colonne NVARCHAR contenant plusieurs centaines d'adresses IP de divers formats
10.12.1.106
192.101.204.1
1.123.12.17
etc... et pour l'instant aucune solution ne fonctionne.

Est-ce que quelqu'un aurait une solution à me proposer?

Par avance merci et cordiales salutations à tous
André

[escartefigue]

Quel est le problème ? Quel est le résultat attendu et le résultat obtenu ?.
Si vous voulez influencer le tri des chaines de caractères, il faut jouer avec la collation : ORDER BY ma_colonne COLLATE ma_collation .

[Darkzinus]

Bonjour, la question est fonctionnelle plus que technique. Quel est le critère de tri sur ces adresses IP ? Est-ce triplet par triplet ?

[StringBuilder]

Déjà, il y a un problème de modélisation.
En effet, une IP est une série de 4 bytes.

Par conséquent, au lieu de stocker ça dans un nvarchar(15) (soit plus de 30 bytes) il aurait été préférable de stocker ça dans un binary(4)

Ceci aurait permis :
- de diviser par 8 l'espace requis pour le stockage de vos IP !
- de corriger d'emblée votre problème de tri
- de permettre de valider que chaque partie de l'IP est bien comprise entre 0 et 255 sans coder quoi que ce soit
- pouvoir effectuer aisément des recherches avec des masques réseaux (qui ne sont au final que des masques binaires)

[SQLpro]

Non, en fait une adresse IP V4 c'est juste un entier de 32 bits…
Il suffit de le coder sous forme d'entier 32 bits et le tri est simple !
C'est la représentation qui en est fait sous forme de 4 petits entiers plus simple à mémoriser/utiliser, tout comme on présente les n° de téléphone par groupe de 2 (France) ou 3 chiffres (US).

Pour information, j'ai posté il y a fort longtemps ce puzzle :
https://sqlpro.developpez.com/Exerci...e=part-3#LIV-H

Qui est très proche de votre problématique….
La solution est la suivante (sous SQL Server) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
--> soit la table 
CREATE TABLE TIP
(TIP_ID     INT PRIMARY KEY,
 TIP_ADR    VARCHAR(15));
GO

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
--> contenant les adresses IP suivantes :
INSERT INTO TIP VALUES (1, '10.120.12.1');
INSERT INTO TIP VALUES (2, '10.130.201.159');
INSERT INTO TIP VALUES (3, '10.130.23.1');
INSERT INTO TIP VALUES (4, '10.130.201.1');
INSERT INTO TIP VALUES (5, '10.13.11.1'); 
INSERT INTO TIP VALUES (6, '10.130.201.5');

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Permet un tel tri. Elle est cependant horriblement couteuse du fait du modèle de données pourri !

Il existe une autre solution via une requête récursive…. Elle est encore moins performante !

A +

[SQLpro]

Et voici la version récursive…

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH
T AS(SELECT TIP_ADR AS TIP_IP, TIP_ID, 2 AS POS,
            CAST(LEFT(TIP_ADR, CHARINDEX('.', TIP_ADR) - 1) AS FLOAT) * POWER(2, 24) AS ADR_IP_NUM,
            RIGHT(TIP_ADR, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) + '.' AS TIP_ADR
     FROM   TIP
     UNION ALL
     SELECT TIP_IP, TIP_ID, POS -1,
            ADR_IP_NUM + CAST(LEFT(TIP_ADR, CHARINDEX('.', TIP_ADR) - 1) AS FLOAT) * POWER(2, 8*POS) AS ADR_IP_NUM,
            RIGHT(TIP_ADR, LEN(TIP_ADR) - CHARINDEX('.', TIP_ADR)) AS TIP_ADR
     FROM   T
     WHERE  POS >= 0
)
SELECT TIP_ID, TIP_IP AS TIP_ADR
FROM   T 
WHERE  POS = -1 
ORDER BY ADR_IP_NUM;

A +