Ordinamento naturale (alfa-numerico umano) in Microsoft SQL 2005

Abbiamo un ampio database su cui abbiamo l’impaginazione laterale DB. Questo è veloce, restituendo una pagina di 50 righe da milioni di record in una piccola frazione di secondo.

Gli utenti possono definire il proprio tipo, in pratica scegliendo la colonna da ordinare. Le colonne sono dinamiche: alcune hanno valori numerici, alcune date e del testo.

Mentre la maggior parte ordina come il testo atteso ordina in modo stupido. Bene, dico stupido, ha senso per i computer, ma frustra gli utenti.

Ad esempio, l’ordinamento in base a un identificatore di stringa fornisce qualcosa come:

rec1 rec10 rec14 rec2 rec20 rec3 rec4 

…e così via.

Voglio che questo tenga conto del numero, quindi:

 rec1 rec2 rec3 rec4 rec10 rec14 rec20 

Non riesco a controllare l’input (altrimenti modifico solo nei primi 000) e non posso contare su un singolo formato: alcuni sono come “{alpha code} – {dept code} – {rec id}”.

Conosco alcuni modi per farlo in C #, ma non riesco a tirare giù tutti i record per ordinarli, in quanto sarebbe rallentare.

Qualcuno conosce un modo per applicare rapidamente un ordinamento naturale nel server Sql?


Stiamo usando:

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

E poi ci stiamo pagando.

Possiamo aggiungere trigger, anche se non lo faremo. Tutti i loro input sono parametrizzati e simili, ma non posso cambiare il formato – se inseriscono “rec2” e “rec10” si aspettano che vengano restituiti proprio così e in ordine naturale.


Abbiamo input utente valido che segue diversi formati per diversi client.

Uno potrebbe andare rec1, rec2, rec3, … rec100, rec101

Mentre un altro potrebbe andare: grp1rec1, grp1rec2, … grp20rec300, grp20rec301

Quando dico che non possiamo controllare l’input intendo che non possiamo forzare gli utenti a cambiare questi standard – hanno un valore come grp1rec1 e non posso riformattarlo come grp01rec001, in quanto cambierebbe qualcosa usato per le ricerche e collegamento a sistemi esterni.

Questi formati variano molto, ma sono spesso miscele di lettere e numeri.

Ordinare questi in C # è facile: basta suddividerlo in { "grp", 20, "rec", 301 } e quindi confrontare i valori di sequenza a turno.

Tuttavia ci possono essere milioni di record e i dati sono paginati, ho bisogno che l’ordinamento sia fatto sul server SQL.

Il server SQL ordina in base al valore, non al confronto: in C # posso dividere i valori per il confronto, ma in SQL ho bisogno di una logica che (molto rapidamente) abbia un singolo valore che ordina in modo coerente.

@moebius – la tua risposta potrebbe funzionare, ma sembra un brutto compromesso aggiungere una chiave di ordinamento per tutti questi valori di testo.

La maggior parte delle soluzioni basate su SQL che ho visto si interrompono quando i dati diventano abbastanza complessi (ad esempio più di uno o due numeri in esso). Inizialmente ho provato ad implementare una funzione di NaturalSort in T-SQL che soddisfaceva le mie esigenze (tra le altre cose, gestisce un numero arbitrario di numeri all’interno della stringa), ma le prestazioni erano troppo lente.

In definitiva, ho scritto una funzione CLR scalare in C # per consentire un ordinamento naturale e, anche con codice non ottimizzato, le prestazioni che chiamano da SQL Server sono incredibilmente veloci. Ha le seguenti caratteristiche:

  • ordinerà correttamente i primi 1000 caratteri (facilmente modificati nel codice o trasformati in un parametro)
  • ordina correttamente i decimali, quindi 123.333 arriva prima di 123.45
  • a causa di quanto sopra, probabilmente NON ordinerà cose come gli indirizzi IP correttamente; se desideri un comportamento diverso, modifica il codice
  • supporta l’ordinamento di una stringa con un numero arbitrario di numeri al suo interno
  • ordinerà correttamente numeri lunghi fino a 25 cifre (facilmente modificati nel codice o trasformati in un parametro)

Il codice è qui:

 using System; using System.Data.SqlTypes; using System.Text; using Microsoft.SqlServer.Server; public class UDF { [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic=true)] public static SqlString Naturalize(string val) { if (String.IsNullOrEmpty(val)) return val; while(val.Contains(" ")) val = val.Replace(" ", " "); const int maxLength = 1000; const int padLength = 25; bool inNumber = false; bool isDecimal = false; int numStart = 0; int numLength = 0; int length = val.Length < maxLength ? val.Length : maxLength; //TODO: optimize this so that we exit for loop once sb.ToString() >= maxLength var sb = new StringBuilder(); for (var i = 0; i < length; i++) { int charCode = (int)val[i]; if (charCode >= 48 && charCode <= 57) { if (!inNumber) { numStart = i; numLength = 1; inNumber = true; continue; } numLength++; continue; } if (inNumber) { sb.Append(PadNumber(val.Substring(numStart, numLength), isDecimal, padLength)); inNumber = false; } isDecimal = (charCode == 46); sb.Append(val[i]); } if (inNumber) sb.Append(PadNumber(val.Substring(numStart, numLength), isDecimal, padLength)); var ret = sb.ToString(); if (ret.Length > maxLength) return ret.Substring(0, maxLength); return ret; } static string PadNumber(string num, bool isDecimal, int padLength) { return isDecimal ? num.PadRight(padLength, '0') : num.PadLeft(padLength, '0'); } } 

Per registrare questo in modo da poterlo chiamare da SQL Server, eseguire i seguenti comandi in Query Analyzer:

 CREATE ASSEMBLY SqlServerClr FROM 'SqlServerClr.dll' --put the full path to DLL here go CREATE FUNCTION Naturalize(@val as nvarchar(max)) RETURNS nvarchar(1000) EXTERNAL NAME SqlServerClr.UDF.Naturalize go 

Quindi, puoi usarlo in questo modo:

 select * from MyTable order by dbo.Naturalize(MyTextField) 

Nota : se si verifica un errore in SQL Server sulla falsariga di Esecuzione del codice utente in .NET Framework disabilitato. Abilita l’opzione di configurazione “clr enabled”. , segui le istruzioni qui per triggersrlo. Assicurati di considerare le implicazioni sulla sicurezza prima di farlo. Se non sei l’amministratore di db, assicurati di discuterne con l’amministratore prima di apportare modifiche alla configurazione del server.

Nota 2 : questo codice non supporta correttamente l’internazionalizzazione (ad esempio, si assume che l’indicatore decimale sia “.”, Non sia ottimizzato per la velocità, ecc. Suggerimenti per migliorarlo sono ben accetti!

Modifica: rinominato la funzione in Naturalizzazione invece di NaturalSort , poiché non esegue alcun ordinamento effettivo.

 order by LEN(value), value 

Non perfetto, ma funziona bene in molti casi.

So che questa è una vecchia domanda ma l’ho appena incontrata e dal momento che non ha una risposta accettata.

Ho sempre usato modi simili a questo:

 SELECT [Column] FROM [Table] ORDER BY RIGHT(REPLICATE('0', 1000) + LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX)))), 1000) 

Le uniche volte in cui questo ha problemi è se la tua colonna non verrà lanciata su un VARCHAR (MAX), o se LEN ([Column])> 1000 (ma puoi modificarlo da 1000 a qualcos’altro se vuoi), ma tu puoi usare questa idea approssimativa per ciò di cui hai bisogno.

Anche questa è una prestazione molto peggiore del normale ORDER BY [Column], ma ti dà il risultato richiesto nell’OP.

Modifica: solo per chiarire ulteriormente, questo sopra non funzionerà se si hanno valori decimali come avere 1 , 1.15 e 1.5 , (verranno ordinati come {1, 1.5, 1.15} ) poiché non è ciò che viene richiesto nel OP, ma ciò può essere fatto facilmente da:

 SELECT [Column] FROM [Table] ORDER BY REPLACE(RIGHT(REPLICATE('0', 1000) + LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX)))) + REPLICATE('0', 100 - CHARINDEX('.', REVERSE(LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX))))), 1)), 1000), '.', '0') 

Risultato: {1, 1.15, 1.5}

E ancora tutto interamente in SQL. Questo non ordinerà gli indirizzi IP perché ora stai entrando in combinazioni di numeri molto specifici rispetto al semplice testo + numero.

La risposta di RedFilter è ottima per dataset di dimensioni ragionevoli in cui l’indicizzazione non è critica, tuttavia se si desidera un indice, sono necessari diversi aggiustamenti.

Innanzitutto, contrassegna la funzione come non facendo alcun accesso ai dati ed essendo deterministica e precisa:

 [SqlFunction(DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None, IsDeterministic = true, IsPrecise = true)] 

Successivamente, MSSQL ha un limite di 900 byte per la dimensione della chiave dell’indice, quindi se il valore naturalizzato è l’unico valore nell’indice, deve essere lungo al massimo 450 caratteri. Se l’indice include più colonne, il valore restituito deve essere ancora più piccolo. Due modifiche:

 CREATE FUNCTION Naturalize(@str AS nvarchar(max)) RETURNS nvarchar(450) EXTERNAL NAME ClrExtensions.Util.Naturalize 

e nel codice C #:

 const int maxLength = 450; 

Infine, dovrai aggiungere una colonna calcasting alla tua tabella, e deve essere persistente (perché MSSQL non può dimostrare che Naturalize è deterministico e preciso), il che significa che il valore naturalizzato è effettivamente memorizzato nella tabella ma è ancora mantenuto automaticamente:

 ALTER TABLE YourTable ADD nameNaturalized AS dbo.Naturalize(name) PERSISTED 

Ora puoi creare l’indice!

 CREATE INDEX idx_YourTable_n ON YourTable (nameNaturalized) 

Ho anche apportato un paio di modifiche al codice di RedFilter: utilizzando caratteri per chiarezza, incorporando la rimozione dello spazio duplicato nel ciclo principale, uscendo una volta il risultato è più lungo del limite, impostando la lunghezza massima senza sottostringhe ecc. Ecco il risultato:

 using System.Data.SqlTypes; using System.Text; using Microsoft.SqlServer.Server; public static class Util { [SqlFunction(DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None, IsDeterministic = true, IsPrecise = true)] public static SqlString Naturalize(string str) { if (string.IsNullOrEmpty(str)) return str; const int maxLength = 450; const int padLength = 15; bool isDecimal = false; bool wasSpace = false; int numStart = 0; int numLength = 0; var sb = new StringBuilder(); for (var i = 0; i < str.Length; i++) { char c = str[i]; if (c >= '0' && c <= '9') { if (numLength == 0) numStart = i; numLength++; } else { if (numLength > 0) { sb.Append(pad(str.Substring(numStart, numLength), isDecimal, padLength)); numLength = 0; } if (c != ' ' || !wasSpace) sb.Append(c); isDecimal = c == '.'; if (sb.Length > maxLength) break; } wasSpace = c == ' '; } if (numLength > 0) sb.Append(pad(str.Substring(numStart, numLength), isDecimal, padLength)); if (sb.Length > maxLength) sb.Length = maxLength; return sb.ToString(); } private static string pad(string num, bool isDecimal, int padLength) { return isDecimal ? num.PadRight(padLength, '0') : num.PadLeft(padLength, '0'); } } 

So che questo è un po ‘vecchio a questo punto, ma nella mia ricerca di una soluzione migliore, mi sono imbattuto in questa domanda. Attualmente sto usando una funzione per ordinare. Funziona perfettamente allo scopo di ordinare i record che sono denominati con caratteri alfanumerici misti (“elemento 1”, “elemento 10”, “elemento 2”, ecc.)

 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 '[az]%' 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,az]%', @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 

Quindi chiama

 select item_name from my_table order by fnMixSort(item_name) 

Triplica facilmente i tempi di elaborazione per una semplice lettura dei dati, quindi potrebbe non essere la soluzione perfetta.

Ecco una soluzione scritta per SQL 2000. Probabilmente può essere migliorata per le versioni SQL più recenti.

 /** * Returns a string formatted for natural sorting. This function is very useful when having to sort alpha-numeric strings. * * @author Alexandre Potvin Latreille (plalx) * @param {nvarchar(4000)} string The formatted string. * @param {int} numberLength The length each number should have (including padding). This should be the length of the longest number. Defaults to 10. * @param {char(50)} sameOrderChars A list of characters that should have the same order. Ex: '.-/'. Defaults to empty string. * * @return {nvarchar(4000)} A string for natural sorting. * Example of use: * * SELECT Name FROM TableA ORDER BY Name * TableA (unordered) TableA (ordered) * ------------ ------------ * ID Name ID Name * 1. A1. 1. A1-1. * 2. A1-1. 2. A1. * 3. R1 --> 3. R1 * 4. R11 4. R11 * 5. R2 5. R2 * * * As we can see, humans would expect A1., A1-1., R1, R2, R11 but that's not how SQL is sorting it. * We can use this function to fix this. * * SELECT Name FROM TableA ORDER BY dbo.udf_NaturalSortFormat(Name, default, '.-') * TableA (unordered) TableA (ordered) * ------------ ------------ * ID Name ID Name * 1. A1. 1. A1. * 2. A1-1. 2. A1-1. * 3. R1 --> 3. R1 * 4. R11 4. R2 * 5. R2 5. R11 */ ALTER FUNCTION [dbo].[udf_NaturalSortFormat]( @string nvarchar(4000), @numberLength int = 10, @sameOrderChars char(50) = '' ) RETURNS varchar(4000) AS BEGIN DECLARE @sortString varchar(4000), @numStartIndex int, @numEndIndex int, @padLength int, @totalPadLength int, @i int, @sameOrderCharsLen int; SELECT @totalPadLength = 0, @string = RTRIM(LTRIM(@string)), @sortString = @string, @numStartIndex = PATINDEX('%[0-9]%', @string), @numEndIndex = 0, @i = 1, @sameOrderCharsLen = LEN(@sameOrderChars); -- Replace all char that have the same order by a space. WHILE (@i <= @sameOrderCharsLen) BEGIN SET @sortString = REPLACE(@sortString, SUBSTRING(@sameOrderChars, @i, 1), ' '); SET @i = @i + 1; END -- Pad numbers with zeros. WHILE (@numStartIndex <> 0) BEGIN SET @numStartIndex = @numStartIndex + @numEndIndex; SET @numEndIndex = @numStartIndex; WHILE(PATINDEX('[0-9]', SUBSTRING(@string, @numEndIndex, 1)) = 1) BEGIN SET @numEndIndex = @numEndIndex + 1; END SET @numEndIndex = @numEndIndex - 1; SET @padLength = @numberLength - (@numEndIndex + 1 - @numStartIndex); IF @padLength < 0 BEGIN SET @padLength = 0; END SET @sortString = STUFF( @sortString, @numStartIndex + @totalPadLength, 0, REPLICATE('0', @padLength) ); SET @totalPadLength = @totalPadLength + @padLength; SET @numStartIndex = PATINDEX('%[0-9]%', RIGHT(@string, LEN(@string) - @numEndIndex)); END RETURN @sortString; END 

Ecco un’altra soluzione che mi piace: http://www.dreamchain.com/sql-and-alpha-numeric-sort-order/

Non è Microsoft SQL, ma da quando sono finito qui quando stavo cercando una soluzione per Postgres, ho pensato che l’aggiunta di questo qui avrebbe aiutato gli altri.

Per i seguenti dati varchar :

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

Questo ha funzionato meglio per me:

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

Se hai problemi a caricare i dati dal DB per ordinare in C #, sono sicuro che rimarrai deluso da qualsiasi approccio nel farlo a livello di codice nel DB. Quando il server sta per ordinare, deve calcolare l’ordine “percepito” proprio come avresti – ogni volta.

Suggerirei di aggiungere una colonna aggiuntiva per memorizzare la stringa ordinabile preelaborata, utilizzando un metodo C #, quando i dati vengono prima inseriti. Potresti provare a convertire i valori numerici in intervalli di larghezza fissa, ad esempio, così “xyz1” diventerebbe “xyz00000001”. Quindi è ansible utilizzare il normale ordinamento di SQL Server.

A rischio di suonare il mio corno, ho scritto un articolo su CodeProject che implementava il problema come proposto nell’articolo CodingHorror. Sentiti libero di rubare dal mio codice .

Ho appena letto un articolo da qualche parte su un argomento del genere. Il punto chiave è: è necessario solo il valore intero per ordinare i dati, mentre la stringa ‘rec’ appartiene all’interfaccia utente. È ansible dividere le informazioni in due campi, ad esempio alfa e num, ordinare per alfa e num (separatamente) e quindi mostrare una stringa composta da alfa + num. È ansible utilizzare una colonna calcasting per comporre la stringa o una vista. Spero che sia d’aiuto

È ansible utilizzare il seguente codice per risolvere il problema:

 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 

saluti, [email protected]

Semplicemente fai da parte

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

Ancora non capisco (probabilmente a causa del mio povero inglese).

Potresti provare:

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

Ma non funzionerà per milioni di record.

Ecco perché ho suggerito di usare il trigger che riempie la colonna separata con il valore umano .

Inoltre:

  • le funzioni T-SQL integrate sono molto lente e Microsoft suggerisce di utilizzare invece le funzioni .NET.
  • il valore umano è costante, quindi non è necessario calcolarlo ogni volta che viene eseguita la query.