Jak porównać zakresowo/sortować wartości tekstowe?

Porównywanie danych numerycznych jest zazwyczaj proste i intuicyjne. Wiadomo – 10 jest zawsze większe od 1 zawsze (chociaż w sumie odkąd interesuję się mechaniką kwantową, nie jestem już taka pewna ). Jednak w świecie baz danych, kiedy przychodzi do porównywania wartości tekstowych, sprawy mogą się skomplikować. 

Porównywanie wartości tekstowych

Zastanówmy się nad tym, jak właściwie porównywać tekst. Na przykład, czy litera "Ą" z polskiego alfabetu znajduje się między "A" a "C"? (Dla osób, które nie znają polskiego alfabetu – spoiler: tak, znajduje się! 😊)

Demo przygotowałam na bazie:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Bez odpowiednich ustawień, porównywanie tekstów może prowadzić do niespodziewanych wyników. Na przykład, niektóre znaki mogą być sortowane w niewłaściwej kolejności, co sprawi, że zapytania do bazy danych nie będą zwracały oczekiwanych rezultatów.

DECLARE
v1 varchar2(2) := 'Ą';

v2 varchar2(1) := 'A';
v3 varchar2(1) := 'C';

begin

if v1 between v2 and v3   then 
    DBMS_OUTPUT.PUT_LINE('ok');
else 
 DBMS_OUTPUT.PUT_LINE('nok');
end if;

end;
/

NOK

Okazuje się, że przy ustawieniach domyślnych Oracle sortuje w nieco inny sposób niż byśmy tego oczekiwali.

Ustawienie lokalizacji

Aby porównywanie wartości tekstowych odbywało się zgodnie z lokalnymi zasadami, musisz ustawić odpowiednie parametry lokalizacji. Na przykład, aby poprawnie porównywać polskie znaki, można użyć następujących komend SQL:

alter session set nls_language = 'POLISH';

NOK

Niestety po uruchomieniu naszego testu - w dalszym ciągu wynik jest NOK. Ustawienie języka nie jest więc rozwiązaniem, którego szukamy.

Parametry sortowania

Żeby uzyskać poprawne sortowanie tekstu należy ustawić parametry NLS_SORT i NLS_COMP:

alter session set nls_sort = 'POLISH'; 
alter session set nls_comp = 'LINGUISTIC';

OK- Sukces! W końcu osiągamy sortowanie o które nam chodziło :)

Innym rozwiązaniem jest podanie bezpośrednio w kodzie ustawień NLS_SORT:

DECLARE
v1 varchar2(2) := 'Ą';

v2 varchar2(1) := 'A';
v3 varchar2(1) := 'C';

begin

  if NLSSORT(v1, 'NLS_SORT=POLISH')  between NLSSORT(v2, 'NLS_SORT=POLISH')  and NLSSORT(v3, 'NLS_SORT=POLISH')  then 
    DBMS_OUTPUT.PUT_LINE('ok');
else 
 DBMS_OUTPUT.PUT_LINE('nok');
end if;

end;
/

Sprawdzić aktualne ustawienia parametrów możemy za pomocą poniższego zapytania:

select * from nls_session_parameters
where parameter in ('NLS_SORT', 'NLS_COMP', 'NLS_LANGUAGE');

PARAMETER                      VALUE                                                           
--------------------------- -------------------
NLS_LANGUAGE                   POLISH                                                          
NLS_SORT                       POLISH                                                          
NLS_COMP                       LINGUISTIC

Parametry:

NLS_COMP:  definiuje zachowanie sortowania

            binary - binary sort [default]

           linguistic - linguistic sort

           ansi - backward compatibility

 

NLS_SORT:  definiuje kolejnosć sortowania [binary/linguistic]