Tri naturel (alpha-numérique humain) dans Microsoft SQL 2005

Question

Nous avons une grande base de données sur laquelle nous avons une pagination côté DB. Celle-ci est rapide, renvoyant une page de 50 lignes à partir de millions d'enregistrements en une petite fraction de seconde.

Les utilisateurs peuvent définir leur propre tri, en choisissant essentiellement la colonne par laquelle ils souhaitent trier. Les colonnes sont dynamiques - certaines ont des valeurs numériques, d'autres des dates et d'autres encore du texte.

Alors que la plupart trient comme prévu, les textes trient d'une manière stupide. Eh bien, je dis stupide, cela a du sens pour les ordinateurs, mais frustre les utilisateurs.

Par exemple, le tri par une chaîne d'identifiant d'enregistrement donne quelque chose comme.. :

rec1
rec10
rec14
rec2
rec20
rec3
rec4

...et ainsi de suite.

Je veux que cela prenne en compte le nombre, donc :

rec1
rec2
rec3
rec4
rec10
rec14
rec20

Je ne peux pas contrôler l'entrée (sinon, je me contenterais de formater en milliers) et je ne peux pas me fier à un format unique - certains sont des choses comme "{code alpha}-{code de département}-{identification du dossier}".

Je connais quelques moyens de le faire en C#, mais je ne peux pas faire descendre tous les enregistrements pour les trier, car cela serait trop lent.

Quelqu'un connaît-il un moyen d'appliquer rapidement un tri naturel dans Sql server ?

---

Nous utilisons :

ROW_NUMBER() over (order by {field name} asc)

Et ensuite on pagine par là.

Nous pouvons ajouter des déclencheurs, mais nous ne le ferions pas. Toutes les entrées sont paramétrées et autres, mais je ne peux pas changer le format - s'ils entrent "rec2" et "rec10", ils s'attendent à ce qu'ils soient renvoyés comme ça, et dans l'ordre naturel.

---

Nous avons des entrées utilisateur valides qui suivent différents formats pour différents clients.

On pourrait dire rec1, rec2, rec3, ... rec100, rec101

Alors qu'un autre pourrait aller : grp1rec1, grp1rec2, ... grp20rec300, grp20rec301

Lorsque je dis que nous ne pouvons pas contrôler l'entrée, je veux dire que nous ne pouvons pas forcer les utilisateurs à modifier ces normes - elles ont une valeur comme grp1rec1 et je ne peux pas la reformater en grp01rec001, car cela reviendrait à modifier un élément utilisé pour les recherches et les liens vers des systèmes externes.

Ces formats varient beaucoup, mais sont souvent des mélanges de lettres et de chiffres.

Le tri en C# est facile - il suffit de le décomposer en { "grp", 20, "rec", 301 } puis comparer les valeurs des séquences à tour de rôle.

Cependant, il peut y avoir des millions d'enregistrements et les données sont paginées, j'ai besoin que le tri soit effectué sur le serveur SQL.

Le serveur SQL trie par valeur, pas par comparaison. En C#, je peux séparer les valeurs pour les comparer, mais en SQL, j'ai besoin d'une logique qui permette d'obtenir (très rapidement) une valeur unique qui trie de manière cohérente.

@moebius - votre réponse pourrait fonctionner, mais il semble que ce soit un vilain compromis d'ajouter une clé de tri pour toutes ces valeurs de texte.

Answer 1

Je sais que cela date un peu, mais dans ma recherche d'une meilleure solution, je suis tombé sur cette question. J'utilise actuellement une fonction pour classer par. Cela fonctionne bien pour trier les enregistrements dont le nom est composé d'un mélange d'alpha et de chiffres ('item 1', 'item 10', 'item 2', etc).

CREATE FUNCTION [dbo].[fnMixSort]
(
    @ColValue NVARCHAR(255)
)
RETURNS NVARCHAR(1000)
AS

BEGIN
    DECLARE @p1 NVARCHAR(255),
        @p2 NVARCHAR(255),
        @p3 NVARCHAR(255),
        @p4 NVARCHAR(255),
        @Index TINYINT

    IF @ColValue LIKE '[a-z]%'
        SELECT  @Index = PATINDEX('%[0-9]%', @ColValue),
            @p1 = LEFT(CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END + REPLICATE(' ', 255), 255),
            @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 255) END
    ELSE
        SELECT  @p1 = REPLICATE(' ', 255)

    SELECT  @Index = PATINDEX('%[^0-9]%', @ColValue)

    IF @Index = 0
        SELECT  @p2 = RIGHT(REPLICATE(' ', 255) + @ColValue, 255),
            @ColValue = ''
    ELSE
        SELECT  @p2 = RIGHT(REPLICATE(' ', 255) + LEFT(@ColValue, @Index - 1), 255),
            @ColValue = SUBSTRING(@ColValue, @Index, 255)

    SELECT  @Index = PATINDEX('%[0-9,a-z]%', @ColValue)

    IF @Index = 0
        SELECT  @p3 = REPLICATE(' ', 255)
    ELSE
        SELECT  @p3 = LEFT(REPLICATE(' ', 255) + LEFT(@ColValue, @Index - 1), 255),
            @ColValue = SUBSTRING(@ColValue, @Index, 255)

    IF PATINDEX('%[^0-9]%', @ColValue) = 0
        SELECT  @p4 = RIGHT(REPLICATE(' ', 255) + @ColValue, 255)
    ELSE
        SELECT  @p4 = LEFT(@ColValue + REPLICATE(' ', 255), 255)

    RETURN  @p1 + @p2 + @p3 + @p4

END

Ensuite, appelez

select item_name from my_table order by fnMixSort(item_name)

Elle permet de tripler facilement le temps de traitement pour une simple lecture de données, et n'est donc pas forcément la solution idéale.

Answer 2

Voici une autre solution qui me plaît : http://www.dreamchain.com/sql-and-alpha-numeric-sort-order/

Il ne s'agit pas de Microsoft SQL, mais comme j'ai atterri ici alors que je cherchais une solution pour Postgres, j'ai pensé que l'ajouter ici pourrait aider d'autres personnes.

EDIT : Voici le code, au cas où le lien disparaîtrait.

CREATE or REPLACE FUNCTION pad_numbers(text) RETURNS text AS $$
  SELECT regexp_replace(regexp_replace(regexp_replace(regexp_replace(($1 collate "C"),
    E'(^|\\D)(\\d{1,3}($|\\D))', E'\\1000\\2', 'g'),
      E'(^|\\D)(\\d{4,6}($|\\D))', E'\\1000\\2', 'g'),
        E'(^|\\D)(\\d{7}($|\\D))', E'\\100\\2', 'g'),
          E'(^|\\D)(\\d{8}($|\\D))', E'\\10\\2', 'g');
$$ LANGUAGE SQL;

"C" est la collation par défaut dans postgresql ; vous pouvez spécifier la collation de votre choix, ou supprimer l'instruction de collation si vous pouvez être certain que les colonnes de votre table ne se verront jamais attribuer une collation non déterministe.

l'usage :

SELECT * FROM wtf w 
  WHERE TRUE
  ORDER BY pad_numbers(w.my_alphanumeric_field)

Answer 3

Pour les points suivants varchar données :

BR1
BR2
External Location
IR1
IR2
IR3
IR4
IR5
IR6
IR7
IR8
IR9
IR10
IR11
IR12
IR13
IR14
IR16
IR17
IR15
VCR

C'est ce qui a le mieux fonctionné pour moi :

ORDER BY substring(fieldName, 1, 1), LEN(fieldName)

Answer 4

Si vous avez du mal à charger les données de la base de données pour les trier en C#, je suis sûr que vous serez déçu par toute approche programmatique dans la base de données. Lorsque le serveur va trier, il doit calculer l'ordre "perçu" comme vous l'auriez fait -- à chaque fois.

Je vous suggère d'ajouter une colonne supplémentaire pour stocker la chaîne triable prétraitée, en utilisant une méthode C#, lors de la première insertion des données. Vous pourriez essayer de convertir les données numériques en plages de largeur fixe, par exemple, de sorte que "xyz1" devienne "xyz00000001". Vous pourriez ensuite utiliser le triage normal du serveur SQL.

Au risque de me vanter, j'ai écrit un article de CodeProject mettant en œuvre le problème tel qu'il est posé dans l'article de CodingHorror. N'hésitez pas à voler de mon code .

Answer 5

Il suffit de trier par

ORDER BY 
cast (substring(name,(PATINDEX('%[0-9]%',name)),len(name))as int)

 ##