Mam bazę danych z kolumną VARCHAR, która zawiera liczby całkowite o różnej długości. Chcę je posortować, aby 10 pojawiło się po 9, a nie 1, a 70A po 70. Mogłem to zrobić za pomocą instrukcji PATINDEX () , CTE i CASE w klauzuli WHERE.

Zastanawiałem się jednak, czy istnieje zestawienie, w którym byłoby to zbędne.

Nie. Sortowanie dotyczy sortowania alfabetycznego, w zależności od strony kodowej, akcentu, wielkości liter, szerokości i kana. Znaki liczbowe (0–9) nie mają żadnych właściwości.

Tak 9jest zawsze po 10Bw dowolnym rodzaju.

Musisz to podzielić, jak zauważyłeś, lub posortować w następujący sposób:

ORDER BY
    RIGHT('                              ' + MyColumn, 30)

Długość po prawej określa, ile masz spacji.

Możesz oczywiście:

• mieć 2 kolumny, aby uczynić to niepotrzebnym (i znacznie szybszym) i mieć kolumnę obliczoną, aby je połączyć
• domagać się zer wiodących
• just just in a char (zapisana wersja mojego PRAWA powyżej)

Te ostatnie 2 sugestie są jak moje PRAWO powyżej i nieco inne. Szybsze sortowanie (nie wymaga przetwarzania colukmn), ale wymagane jest więcej pamięci

Chciałbym Skonfiguruj obliczane kolumny następnie rodzaj na podstawie tego. Coś jak

CAST( 
     CASE WHEN IS_NUMERIC(left(OtherColumn, 2) = 1) then 
         left(OtherColumn,2) 
     else 
         left(otherColumn, 1)  
AS INT)

Następnie użyj tej kolumny, aby posortować według, ponieważ możesz teraz indeksować kolumnę.

Jeśli potrzebujesz bolesnego sposobu na udowodnienie, co mówi @gbn (zasadniczo, że nie możesz powiedzieć sortowaniu, aby inaczej zamawiało podciągi), możesz utworzyć szybką tabelę #temp, która ma współczynnik dla oczekiwanej kolejności, i sprawdzić, czy zamawianie według dowolnego zestawienia zwraca to samo zamówienie:

CREATE TABLE #foo(id INT, n NVARCHAR(10));

CREATE TABLE #bar(collation SYSNAME);

SET NOCOUNT ON;

INSERT #foo SELECT 1,'1'
UNION SELECT 2,'2'
UNION SELECT 3,'3'
UNION SELECT 4,'6'
UNION SELECT 5,'10'
UNION SELECT 6,'10A'
UNION SELECT 7,'10B'
UNION SELECT 8,'11';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
    WITH x AS 
    (
        SELECT n, rn = ROW_NUMBER() OVER 
        (ORDER BY n COLLATE ' + name + ') FROM #foo
    ) 
    INSERT #bar 
    SELECT TOP (1) ''' + name + ''' FROM x
    WHERE NOT EXISTS
    (
        SELECT COUNT(*) FROM #foo AS f
        WHERE f.id = x.rn
        AND f.n <> x.n
    );' FROM sys.fn_helpcollations();

EXEC sp_executesql @sql;

SELECT collation FROM #bar;

GO
DROP TABLE #foo, #bar;

Działa to dla mnie za około 10 sekund i daje 0 wierszy - co oznacza, że ​​żadne sortowanie niedostępne dla SQL Server (przynajmniej 2008 R2, nie próbowałem Denali) posortuje w sposób zgodny z oczekiwaniami. Potrzebujesz innego sposobu zdefiniowania sortowania.

Chcesz rozsądnego i wydajnego sposobu sortowania liczb w ciągach jako liczb rzeczywistych? Rozważ głosowanie na moją sugestię Microsoft Connect: Obsługa „naturalnego sortowania” / DIGITSASNUMBERS jako opcji sortowania

Chociaż to pytanie jest specyficzne dla programu SQL Server, a ta odpowiedź nie jest, uważam, że nadal powinienem publikować te informacje, aby zwiększyć ich świadomość i nie sprzeciwiać się żadnej z pozostałych odpowiedzi.

Biorąc to pod uwagę, poza SQL Server, w niektórych środowiskach możliwe jest sortowanie tego typu. Jest to coś, co jest przynajmniej określone w dokumentacji Unicode. W JĘZYKU MARKUPU DANYCH UNICODE LOCALE (LDML) CZĘŚĆ 5: Standard / raport COLLATION znajduje się tabela ustawień sortowania, która opisuje różne opcje dostosowywania sortowania. Jedną z opcji jest -kn-truelub [numericOrdering on]:

Jeśli ta opcja jest włączona , każda sekwencja cyfr dziesiętnych (General_Category = Nd w [ UAX44 ]) jest sortowana na poziomie podstawowym za pomocą jej wartości liczbowej. Na przykład „A-21” <„A-123”. Obliczone wagi pierwotne znajdują się na początku grupy zmiany kolejności cyfr . Zatem z nieprzydzieloną tabelą UCA, „a $” <„a0” <„a2” <„a12” <„a⓪” <„aa”.

Jednak ten dokument jest „standardem technicznym” i nie jest częścią podstawowej specyfikacji Unicode. Uwaga na górze dokumentu brzmi:

Unicode Technical Standard (UTS) to niezależna specyfikacja. Zgodność ze standardem Unicode nie oznacza zgodności z żadnym UTS.

Dlatego to szczególne zachowanie nie jest dostępne w SQL Server, a nawet w .NET (przynajmniej nie natywnie), mimo że oba są zgodne z podstawową specyfikacją Unicode.

Projekt ICU (International Components for Unicode) to zestaw bibliotek C / C ++ i Java, które implementują tę funkcjonalność, a jest nawet demo online. A w „powiązanych projektach” znajduje się link do projektu .NET, który wydaje się być opakowaniem obiektów COM dla biblioteki ICU, który pozwoliłby na ujawnienie tej funkcji w zarządzanym kodzie. Ale nie jest jasne, czy ten projekt .NET jest nadal aktywny.

Ale aby zobaczyć to zachowanie w akcji, przejdź do demonstracji ICU Collation .

Wklej następujące elementy w polu Tekst wejściowy po lewej stronie:

1
2
10B
6
11
10A
3
10

Ustaw wszystkie opcje na „domyślne”. Zaznacz opcję „wprowadź numery linii” po prawej stronie sortprzycisku i upewnij się, że opcja „siły różnic” nie jest zaznaczona.

Kliknij sortprzycisk i powinieneś uzyskać następujące informacje:

[1] 1
[8] 10
[6] 10A
[3] 10B
[5] 11
[2] 2
[7] 3
[4] 6

Tego należy się spodziewać podczas typowego sortowania ciągów i tego, co widzisz w SQL Server.

Teraz, w serii przycisków radiowych tuż nad sortprzyciskiem, drugi rząd jest oznaczony jako „numeryczny”. Wybierz przycisk radiowy „on”.

Kliknij sortprzycisk ponownie i powinieneś uzyskać następujące informacje:

[1] 1
[2] 2
[7] 3
[4] 6
[8] 10
[6] 10A
[3] 10B
[5] 11

Pytanie, czy to działa, gdy część liczbowa znajduje się w środku ciągu? Ok, wklej następujące elementy w polu Tekst wejściowy po lewej stronie (zastępując poprzednią listę):

Script - 1.sql
Script - 2.sql
Script - 10B.sql
Script - 6.sql
Script - 11.sql
Script - 10A.sql
Script - 3.sql
Script - 10.sql

Upewnij się, że ustawienie numeryczne jest nadal ustawione na „włączone”. Kliknij sortprzycisk ponownie i powinieneś uzyskać następujące informacje:

[1] Script - 1.sql
[2] Script - 2.sql
[7] Script - 3.sql
[4] Script - 6.sql
[8] Script - 10.sql
[6] Script - 10A.sql
[3] Script - 10B.sql
[5] Script - 11.sql

Chcesz to zobaczyć w innym miejscu? Utwórz folder na dysku twardym, na przykład C: \ temp \ sorting \ , i utwórz puste pliki o tych samych nazwach „Skrypt -...”. Wykonaj DIRpolecenie w oknie poleceń, a zobaczysz standardowe sortowanie. Ale patrząc na listę plików w Eksploratorze Windows zobaczysz listę posortowaną za pomocą opcji „numerycznej” :-).