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

Vous pouvez utiliser le code suivant pour résoudre le problème :

Select *, 
    substring(Cote,1,len(Cote) - Len(RIGHT(Cote, LEN(Cote) - PATINDEX('%[0-9]%', Cote)+1)))alpha,
    CAST(RIGHT(Cote, LEN(Cote) - PATINDEX('%[0-9]%', Cote)+1) AS INT)intv 
FROM Documents 
   left outer join Sites ON Sites.IDSite = Documents.IDSite 
Order BY alpha, intv

regards, rabihkahaleh@hotmail.com

Answer 2

Je viens de lire un article quelque part sur ce sujet. Le point essentiel est le suivant : vous n'avez besoin que de la valeur entière pour trier les données, tandis que la chaîne "rec" appartient à l'interface utilisateur. Vous pourriez diviser l'information en deux champs, disons alpha et num, trier par alpha et num (séparément) et ensuite afficher une chaîne composée de alpha + num. Vous pourriez utiliser une colonne calculée pour composer la chaîne, ou une vue. J'espère que cela vous aidera.

Answer 3

Que signifie exactement vous ne pouvez pas contrôler l'entrée, mais vous pouvez commander dans le serveur SQL ?

Si vous savez comment le faire en C#, le mieux est peut-être d'écrire une fonction .NET qui renvoie une valeur prête à être triée en manière humaine (voir Réponse d'OrbMan pour plus de détails).

Ensuite, appelez-la dans un trigger sur INSERT, UPDATE et stockez ce que la fonction C# renvoie dans une colonne séparée (ou utilisez simplement une colonne calculée persistante) et ordonnez par cette valeur.

Ou utilisez RANK_OVER() avec la fonction C#.

Answer 4

Je ne comprends toujours pas (probablement à cause de mon mauvais anglais).

Vous pouvez essayer :

ROW_NUMBER() OVER (ORDER BY dbo.human_sort(field_name) ASC)

Mais ça ne marchera pas pour des millions d'enregistrements.

C'est pourquoi j'ai suggéré d'utiliser un déclencheur qui remplit séparé colonne avec valeur humaine .

De plus :

• fonctions T-SQL intégrées sont vraiment très lentes et Microsoft suggère d'utiliser les fonctions .NET à la place.
• valeur humaine est constante, il est donc inutile de la calculer à chaque fois lorsque la requête est exécutée.