Funkcje tekstowe w SQLite: SUBSTR, REPLACE, INSTR i inne

Większość prawdziwych zapytań kręci się wokół tekstu

Liczby są proste. Problemy zaczynają się przy tekście: imiona z przypadkowymi spacjami, adresy e-mail pisane raz wielkimi, raz małymi literami, identyfikatory sklejone myślnikami, pola z wolnym tekstem, które pasują prawie, ale nie do końca. SQLite ma niewielki, konkretny zestaw funkcji tekstowych, który radzi sobie z większością takich przypadków bez kodu aplikacji.

Na tej stronie omawiamy te, po które sięgniesz najpierw: łączenie, wycinanie, wyszukiwanie, zamianę, przycinanie i formatowanie.

Łączenie napisów to ||, a nie CONCAT

SQLite nie ma funkcji CONCAT. Napisy łączy się operatorem ||:

SELECT 'Hello, ' || 'world!' AS greeting;

SELECT 'User #' || 42 || ' joined' AS message;

Liczby i inne typy są automatycznie zamieniane na tekst. Haczyk: jeśli którykolwiek operand to NULL, całe wyrażenie staje się NULL. To standardowe zachowanie SQL, ale wiele osób zaskakuje:

SELECT 'Hello, ' || NULL || '!' AS greeting;

SELECT 'Hello, ' || COALESCE(NULL, 'friend') || '!' AS safe_greeting;

Owiń kolumny, które mogą być puste, w COALESCE(col, '') albo COALESCE(col, 'default'), jeśli brakująca wartość nie ma zniszczyć całego napisu.

LENGTH, UPPER, LOWER

Te trzy funkcje będziesz używać bez przerwy:

SELECT
    LENGTH('résumé')        AS char_count,
    UPPER('hello world')    AS upper_case,
    LOWER('HELLO WORLD')    AS lower_case;

LENGTH zwraca liczbę znaków tekstu, a nie bajtów. Jeśli naprawdę potrzebujesz bajtów (rzadko, ale przydaje się przy analizie rozmiaru danych), użyj OCTET_LENGTH. UPPER i LOWER domyślnie zmieniają tylko litery ASCII: znaki z akcentami przechodzą bez zmian, chyba że załadujesz rozszerzenie ICU.

SUBSTR: wycinanie fragmentów napisu

SUBSTR(text, start, length) wyciąga fragment napisu. Indeksy zaczynają się od 1: 1 to pierwszy znak, a nie 0:

SELECT
    SUBSTR('codecademy', 1, 4)  AS first_four,
    SUBSTR('codecademy', 5)     AS rest,
    SUBSTR('codecademy', -3)    AS last_three;

Kilka rzeczy, o których warto pamiętać:

• Trzeci argument jest opcjonalny. Bez niego dostajesz wszystko od start do końca.
• Ujemny start liczy od końca napisu.
• Jeśli start wypada za końcem napisu, dostajesz pusty napis, a nie błąd.

SUBSTRING działa jako synonim, na wypadek gdyby twoje nawyki pochodziły z innej bazy danych.

INSTR: szukanie fragmentu

INSTR(haystack, needle) zwraca pozycję (liczoną od 1) pierwszego wystąpienia needle w haystack albo 0, jeśli nic nie znaleziono:

SELECT
    INSTR('user@example.com', '@')        AS at_position,
    INSTR('user@example.com', 'xyz')      AS not_found,
    SUBSTR('user@example.com',
           1,
           INSTR('user@example.com', '@') - 1) AS local_part;

Ostatnie wyrażenie to typowy w SQLite sposób na „wszystko przed @”: znajdź separator przez INSTR, a potem wytnij fragment przez SUBSTR. To połączenie będziesz pisać często. Zwróć uwagę, że INSTR zwraca 0, gdy nic nie pasuje, więc sprawdź to przed wycinaniem: podanie 0 do SUBSTR po cichu daje dziwne wyniki.

REPLACE: zamiana jednego fragmentu na inny

REPLACE(text, old, new) zamienia każde wystąpienie old na new:

SELECT
    REPLACE('555-123-4567', '-', '')                AS digits_only,
    REPLACE('Hello, world. Hello, again.',
            'Hello',
            'Hi')                                   AS shortened,
    REPLACE('  spaced  out  ', ' ', '')             AS no_spaces;

Funkcja rozróżnia wielkość liter i nie przyjmuje wyrażeń regularnych, tylko dosłowny fragment tekstu. Przy bardziej złożonych przekształceniach możesz łączyć kolejne wywołania REPLACE, ale gdy zagnieżdżasz ich więcej niż dwa czy trzy, czas przenieść tę pracę do aplikacji.

TRIM, LTRIM, RTRIM

Dane wpisywane przez użytkowników często mają spacje na początku i na końcu. TRIM je usuwa:

SELECT
    TRIM('   hello   ')              AS both_sides,
    LTRIM('   hello   ')             AS left_only,
    RTRIM('   hello   ')             AS right_only,
    TRIM('---hello---', '-')         AS custom_chars;

Domyślnie funkcje usuwają spacje. Podaj drugi argument, żeby wskazać, które znaki usunąć: każdy znak z drugiego argumentu jest traktowany jako element „zbioru do usunięcia”, a nie jako dosłowny fragment. Dlatego TRIM('xxxhelloxx', 'x') daje 'hello'.

printf: formatowanie liczb i napisów

Gdy potrzebujesz sformatowanego napisu (stała liczba miejsc po przecinku, liczby z zerami wiodącymi, zapis szesnastkowy), użyj printf (dostępnej też jako format):

SELECT
    printf('%.2f',         3.14159)            AS two_decimals,
    printf('%05d',         42)                 AS padded,
    printf('$%,.2f',       1234567.89)         AS money,
    printf('%s is %d',     'Ada',  30)         AS sentence;

Specyfikatory formatu działają jak w C, czyli %d, %s, %f, %x, dopełnianie zerami lub spacjami i tak dalej. To dużo czytelniejsze niż budowanie napisów przez || i stos CASTów.

LIKE vs GLOB: dopasowywanie wzorców

Dwa operatory, dwa różne światy.

LIKE używa klasycznych symboli wieloznacznych SQL: % oznacza dowolny ciąg znaków, _ pojedynczy znak. Nie rozróżnia wielkości liter dla ASCII:

SELECT 'Alice'  LIKE 'a%'      AS alice_match;
SELECT 'README' LIKE '_____'   AS five_chars;
SELECT 'foo.txt' LIKE '%.txt'  AS is_text_file;

GLOB używa symboli z powłoki Unix: * oznacza dowolny ciąg, ? pojedynczy znak, [abc] klasę znaków. Rozróżnia wielkość liter:

SELECT 'Alice'  GLOB 'A*'         AS alice_match;
SELECT 'Alice'  GLOB 'a*'         AS lowercase_match;
SELECT 'log7'   GLOB 'log[0-9]'   AS digit_class;

Zasada wyboru: LIKE do dopasowań w ludzkim stylu, takich jak „zaczyna się od”, „zawiera”, „kończy się na”. GLOB, gdy liczy się wielkość liter albo potrzebujesz klas znaków. Oba mogą korzystać z indeksów, ale tylko wtedy, gdy wzorzec jest zakotwiczony na początku ('foo%', a nie '%foo'): symbol wieloznaczny na początku wymusza pełne skanowanie.

Dzielenie napisów: SPLIT nie istnieje

SQLite nie ma funkcji SPLIT_STRING. Są dwa praktyczne obejścia:

-- Wyciągnij jeden fragment przez INSTR + SUBSTR
WITH addresses AS (
    SELECT 'rosa@example.com'   AS email UNION ALL
    SELECT 'boris@coddy.tech'   UNION ALL
    SELECT 'ada@lovelace.org'
)
SELECT
    SUBSTR(email, 1, INSTR(email, '@') - 1)  AS local_part,
    SUBSTR(email, INSTR(email, '@') + 1)     AS domain
FROM addresses;

Aby podzielić tekst według separatora na wiele wierszy, najczytelniej jest użyć json_each na tablicy JSON albo rekurencyjnego CTE. Oba sposoby omówimy w kolejnych rozdziałach. Na razie zapamiętaj, że „daj mi każde słowo” nie jest w SQLite jednolinijkowcem.

Przykład w praktyce: porządkowanie imion

Złóżmy to razem. Wyobraź sobie tabelę users z nieuporządkowanymi nazwami wyświetlanymi: nadmiarowe spacje, pomieszana wielkość liter, opcjonalne tytuły w rodzaju "Dr. " albo "Mr. ", które chcesz usunąć:

WITH users(id, display_name) AS (
    VALUES
      (1, '  Dr. Ada Lovelace  '),
      (2, 'mr. Boris Karloff'),
      (3, 'Rosa  Parks'),
      (4, '   alan TURING')
)
SELECT
    id,
    display_name AS original,
    TRIM(
      REPLACE(
        REPLACE(LOWER(TRIM(display_name)), 'dr. ', ''),
        'mr. ', ''
      )
    ) AS cleaned
FROM users;

Wyrażenie czyta się od środka: usuń spacje z brzegów, zamień na małe litery, usuń tytuły, a potem przytnij jeszcze raz na wypadek, gdyby po usunięciu tytułu została spacja na początku. Każdy krok to jedna funkcja: złożoność bierze się z ich nakładania. Gdy stos rośnie ponad trzy czy cztery poziomy, to znak, żeby użyć kolumny generowanej (rozdział: funkcje zaawansowane) albo czyścić dane podczas importu.

Co warto zapamiętać

• || do łączenia napisów; NULL psuje wynik, więc używaj COALESCE.
• SUBSTR i INSTR razem pokrywają większość potrzeb typu „znajdź i wytnij”.
• REPLACE zamienia każde wystąpienie dosłownego fragmentu.
• TRIM i pokrewne funkcje przyjmują własny zbiór znaków, nie tylko białe znaki.
• printf to właściwe narzędzie do sformatowanego wyniku.
• LIKE do symboli wieloznacznych SQL bez rozróżniania wielkości liter, GLOB do wzorców w stylu powłoki z rozróżnianiem wielkości liter.

Dalej: funkcje liczbowe

Skoro tekst mamy za sobą, naturalnym kolejnym krokiem są liczby: zaokrąglanie, wartości bezwzględne, pułapki dzielenia i funkcje matematyczne dodane w nowszych wersjach SQLite. O tym jest następna strona.