Trier correctement les nombres en pointillés stockés en tant que caractère dans SQL Server

Question

J'ai une table SQL qui stocke un numéro d'article personnalisé. Chacun d'entre eux peut avoir un enfant séparé de lui avec un séparateur de . . Chacun d'entre eux peut aussi avoir un enfant.

Un exemple de ce qu'il pourrait être (encore une fois, dynamique, on ne sait pas ce qu'il sera) :

Item Number
1
1.1
1.1.1
1.1.1.1
1.1.1.1.a
1.1.1.1.b
10
11
2.1
2.10
2.2
2.20
20
3
30

Ce qui rend la chose difficile, c'est que ces chiffres sont créés à la volée et pas nécessairement dans l'ordre. Vous pouvez créer 5 nombres (1, 2, 3, 4, 5) et ensuite créer un enfant de 1 qui ne sera pas stocké dans l'ordre dans la base de données.

Comment puis-je sélectionner dans le tableau et ordonner par le Item Number pour qu'il s'affiche correctement, comme ci-dessus, lorsque les données ne sont pas stockées dans cet ordre ?

La plupart des solutions que j'ai essayées me donnent soit 1, 2, 3, 4, 5...1.1, 1.2 OR 1, 1.1, 1.1.1, 10, 11...2, 2.1, 20....3, 30, etc .

Answer 1

Si vous avez SQL 2008, vous pouvez utiliser le nouveau type de données hierarchyid :

WITH Items (ItemNumber) AS (
    SELECT '1' UNION ALL SELECT '1.1' UNION ALL SELECT '1.1.1'
    UNION ALL SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '2'
    UNION ALL SELECT '2.1' UNION ALL SELECT '20' UNION ALL SELECT '3'
    UNION ALL SELECT '30'
)
SELECT *
FROM Items 
ORDER BY Convert(hierarchyid, '/' + ItemNumber + '/');

Answer 2

J'en ai discuté dans un autre forum où nous avons proposé une solution XML très dynamique. Adam Haines nous a aidés à l'optimiser, ce qui a considérablement amélioré les performances. Cette version inclut un correctif pour trier correctement les chiffres alphabétiques.

Étant donné les valeurs suivantes :

declare @temp table (id varchar(255))

insert into @temp (id) values
  ('1.1.a.1'),('1.1.aa.2'),
  ('1.1.b.3'),('1.1.a.4'),
  ('1.1.a.5'),('1.1.a.6'),
  ('1.1.a.7'),('1.1.a.8'),
  ('1.1.a.9'),('1.1.a.10'),
  ('1.1.a.11'),('1.1.b.1'),
  ('1.1.b.2'),('1.2.a.1'),
  ('1.10.a.1'),('1.11.a.1'),
  ('1.20.a.1'),('101.20.a.2'),
  ('1.20.a.150'),('1.1'),
  ('1.2'),('1')

Cette requête :

declare @xml xml,
        @max_len int

set @xml =
(
select id as id, cast('<i>' + replace(id,'.','</i><i>') + '</i>' as xml)
from @temp
for xml path('id_root'),type
)

select @max_len = max(len(x.i.value('.','varchar(10)')))
from @xml.nodes('/id_root/i') x(i)

select [id]--, srt.srtvalue
from @temp
cross apply(
    select case when ISNUMERIC(x.i.value('.','varchar(10)')) = 1 then right(replicate('0',@max_len) + x.i.value('.','varchar(10)'),@max_len) else x.i.value('.','varchar(10)') end + '.'
    from @xml.nodes('/id_root/i') x(i)
    where x.i.value('../id[1]','varchar(50)') = [@temp].id
    for xml path('')
) as srt(srtvalue)
order by srt.srtvalue

Renvoie ces valeurs :

id
1
1.1
1.1.a.1
1.1.a.4
1.1.a.5
1.1.a.6
1.1.a.7
1.1.a.8
1.1.a.9
1.1.a.10
1.1.a.11
1.1.aa.2
1.1.b.1
1.1.b.2
1.1.b.3
1.2
1.2.a.1
1.10.a.1
1.11.a.1
1.20.a.1
1.20.a.150
101.20.a.2

Si vous avez plus de 10 caractères dans un seul chiffre, vous devrez modifier le varchar(10) en conséquence.

-- James

Answer 3

Si vous voulez trier les choses numériquement, ne les stockez pas en tant que nvarchar.

En ACTUEL La solution est de faire de ces chiffres leur propre int champs pour dire Version , Versiona , Versionb ...

Puis ORDER BY Version, Versiona, Versionb

Si vous stockez des nombres en tant que caractères, ne vous attendez pas à ce qu'ils fonctionnent comme des nombres.

Answer 4

Il s'agit plus d'une blague que d'une vraie réponse. Si

• vos catégories ont au maximum 4 niveaux
• vous ne vous souciez vraiment pas de la performance

alors essayez ceci :

WITH Items (ItemNumber) AS (
              SELECT '1' UNION ALL SELECT '1.1' UNION ALL SELECT '1.1.1'
    UNION ALL SELECT '-1' UNION ALL SELECT '1.-1' UNION ALL SELECT '1.-1.1'
    UNION ALL SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '2'
    UNION ALL SELECT '1.2000' UNION ALL SELECT '1.-2000' UNION ALL SELECT '2.1'
    UNION ALL SELECT '2.2' UNION ALL SELECT '20' UNION ALL SELECT '3'
    UNION ALL SELECT '30' UNION ALL SELECT '30.1' UNION ALL SELECT '10.10'
    UNION ALL SELECT '1.-10' UNION ALL SELECT '1.1.1.1'
)

SELECT ItemNumber
FROM 
  ( SELECT
          ItemNumber
        , CASE WHEN ItemNumber LIKE '%.%.%.%' THEN ItemNumber
               WHEN ItemNumber LIKE '%.%.%' THEN ItemNumber + '.0'
               WHEN ItemNumber LIKE '%.%' THEN ItemNumber + '.0.0'
               ELSE ItemNumber + '.0.0.0'
          END AS ItemNumberToParse
    FROM Items
  ) AS tmp
ORDER BY CAST(PARSENAME(ItemNumberToParse, 4) AS INT),
         CAST(PARSENAME(ItemNumberToParse, 3) AS INT),
         CAST(PARSENAME(ItemNumberToParse, 2) AS INT),
         CAST(PARSENAME(ItemNumberToParse, 1) AS INT) ;

Résultat :

  ItemNumber
    -1
    1.-2000
    1.-10
    1.-1
    1.-1.1
    1
    1.1
    1.1.1
    1.1.1.1
    1.2000
    2
    2.1
    2.2
    3
    10
    10.10
    11
    20
    30
    30.1

Answer 5

Quelques questions :

• Combien de sous-catégories peut-il y avoir ?
• Seront-ils toujours de simples chiffres ou pourront-ils un jour être des lettres ?
• Quel est le plus grand nombre qui puisse être une valeur unique entre des points ?

Si vous utilisez SQL 2008, je recommande la réponse de @Vito car c'est de loin la meilleure solution.

Si vous utilisez une version antérieure, vous devrez faire un peu de travail.

Voici une version de SQL 2005. J'ai supposé que les réponses aux questions ci-dessus sont 100, toujours des chiffres, et 9999999999 (10 chiffres).

WITH Items (ItemNumber) AS (
    SELECT '1' UNION ALL SELECT '1.1' UNION ALL SELECT '1.1.1'
    UNION ALL SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '2'
    UNION ALL SELECT '2.1' UNION ALL SELECT '20' UNION ALL SELECT '3'
    UNION ALL SELECT '30' UNION ALL SELECT '9999999999.9999999999'
), Padded AS (
   SELECT
      ItemNumber,
      Convert(nvarchar(max), '') SortValue,
      ItemNumber Remainder,
      0 Selector
   FROM Items
   UNION ALL
   SELECT
      ItemNumber,
      SortValue + Right('000000000' + Left(Remainder, CharIndex('.', Remainder + '.') - 1), 10),
      Substring(Remainder, CharIndex('.', Remainder + '.') + 1, 2147483647),
      CASE WHEN Remainder LIKE '%.%' THEN 0 ELSE 1 END
   FROM Padded
   WHERE
      Remainder <> ''
)
SELECT ItemNumber
FROM Padded
WHERE Selector = 1
ORDER BY SortValue;

Pour SQL 2000, ça va être un peu plus difficile...