Строковые функции SQLite: SUBSTR, REPLACE, INSTR

Строки — это там, где живут реальные запросы

С числами всё просто. А вот строки — это где запросы превращаются в кашу: имена с лишними пробелами, email в разном регистре, ID, склеенные через дефисы, поля со свободным текстом, которые почти-но-не-совсем совпадают. В SQLite есть небольшой, но хорошо продуманный набор строковых функций, который покрывает большую часть таких задач без участия прикладного кода.

На этой странице разберём те, к которым тянется рука первым делом: конкатенация, вырезание подстрок, поиск, замена, обрезка пробелов и форматирование.

Конкатенация строк в SQLite — это ||, а не CONCAT

В SQLite нет функции CONCAT. Строки склеиваются оператором ||:

SELECT 'Привет, ' || 'мир!' AS greeting;

SELECT 'Пользователь №' || 42 || ' присоединился' AS message;

Числа и значения других типов сами приводятся к тексту. Но есть нюанс: если хотя бы один операнд — NULL, всё выражение тоже превращается в NULL. Это стандартное поведение SQL, но многих оно застаёт врасплох:

SELECT 'Привет, ' || NULL || '!' AS greeting;

SELECT 'Привет, ' || COALESCE(NULL, 'друг') || '!' AS safe_greeting;

Оборачивайте nullable-столбцы в COALESCE(col, '') или COALESCE(col, 'default'), если не хотите, чтобы одно пропущенное значение обнулило всю строку.

LENGTH, UPPER, LOWER

Эта тройка пригодится вам буквально каждый день:

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

LENGTH возвращает количество символов в тексте, а не байт. Если вам нужны именно байты (редкий случай, но полезный для анализа объёма хранения), используйте OCTET_LENGTH. Функции UPPER и LOWER по умолчанию преобразуют только ASCII-буквы — символы с диакритикой остаются без изменений, пока вы не подключите расширение ICU.

SUBSTR: вырезаем подстроку

Функция SUBSTR(text, start, length) извлекает фрагмент строки. Индексация в SQLite начинается с единицы — 1 означает первый символ, а не 0:

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

Несколько моментов, которые стоит запомнить:

• Третий аргумент необязателен. Если его не указать, вернётся всё от позиции start до конца строки.
• Отрицательный start отсчитывается от конца строки.
• Если start оказался за пределами строки, вы получите пустую строку, а не ошибку.

В качестве синонима принимается SUBSTRING — пригодится, если мышечная память тянет вас к другой СУБД.

INSTR: поиск подстроки в SQLite

INSTR(haystack, needle) возвращает позицию первого вхождения needle в haystack (нумерация с единицы) или 0, если подстрока не найдена:

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;

Это классическая идиома SQLite для «всего, что идёт до @»: находим разделитель через INSTR, а затем вырезаем нужный кусок с помощью SUBSTR. Такую связку вы будете писать постоянно. Учтите один момент: если совпадений нет, INSTR возвращает 0, и перед вырезанием это стоит проверить — передача 0 в SUBSTR молча выдаёт странный результат.

REPLACE: замена одной подстроки на другую

Функция REPLACE(text, old, new) заменяет каждое вхождение old на new:

SELECT
    REPLACE('555-123-4567', '-', '')                AS digits_only,
    REPLACE('Привет, мир. Привет, снова.',
            'Привет',
            'Здравствуй')                           AS shortened,
    REPLACE('  пробелы  везде  ', ' ', '')          AS no_spaces;

Учтите: функция чувствительна к регистру и регулярные выражения не понимает — только точное вхождение подстроки. Для более сложных преобразований можно сцепить несколько REPLACE подряд, но если вложенность переваливает за два-три уровня — пора выносить логику в приложение.

TRIM, LTRIM, RTRIM

Данные, которые ввёл пользователь, частенько приходят с пробелами по краям. TRIM их подчищает:

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

По умолчанию они убирают пробелы. Если нужно вырезать другие символы, передайте их вторым аргументом — причём каждый символ из этого аргумента воспринимается как элемент «множества для удаления», а не как цельная подстрока. Например, TRIM('xxxhelloxx', 'x') вернёт 'hello'.

printf: форматирование чисел и строк

Когда нужна красиво оформленная строка — фиксированное число знаков после запятой, выравнивание чисел нулями или вывод в шестнадцатеричном виде — на помощь приходит printf (он же 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;

Спецификаторы формата здесь — те же, что в C: %d, %s, %f, %x, дополнение нулями или пробелами и так далее. Получается куда чище, чем городить строку через || и кучу CAST-ов.

LIKE vs GLOB: поиск по шаблону

Два оператора — два разных мира.

LIKE работает с классическими SQL-подстановочными символами: % — любая последовательность символов, _ — один символ. Для ASCII регистр не учитывается:

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

GLOB работает с шаблонами в стиле Unix shell: * — любая последовательность символов, ? — ровно один символ, [abc] — класс символов. При этом регистр учитывается:

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

Правило выбора простое: LIKE — для «человеческих» запросов в духе «начинается с», «содержит», «заканчивается на». GLOB — когда важен регистр или нужны классы символов. Оба умеют пользоваться индексом, но только если шаблон привязан к началу строки ('foo%', а не '%foo'): ведущий wildcard заставляет SQLite пройтись по всей таблице.

Как разбить строку в SQLite: функции SPLIT здесь нет

В SQLite нет встроенной функции SPLIT_STRING. На практике обходятся двумя способами:

-- Извлекаем отдельную часть с помощью 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;

Если нужно разбить строку по разделителю на несколько строк результата, самый чистый вариант — это json_each поверх JSON-массива или рекурсивный CTE. К обоим подходам мы ещё вернёмся в следующих главах, а пока просто держите в голове: в SQLite «дай мне каждое слово» одной строчкой не пишется.

Боевой пример: чистим имена пользователей

Соберём всё вместе. Допустим, есть таблица users с неаккуратными отображаемыми именами: лишние пробелы, разнобой в регистре, плюс опциональные приставки вроде "Dr. " или "Mr. ", от которых хочется избавиться:

WITH users(id, display_name) AS (
    VALUES
      (1, '  Др. Ада Лавлейс  '),
      (2, 'г-н Борис Карлофф'),
      (3, 'Роза  Паркс'),
      (4, '   алан ТЬЮРИНГ')
)
SELECT
    id,
    display_name AS original,
    TRIM(
      REPLACE(
        REPLACE(LOWER(TRIM(display_name)), 'др. ', ''),
        'г-н ', ''
      )
    ) AS cleaned
FROM users;

Выражение читается изнутри наружу: сначала срезаем пробелы по краям, переводим в нижний регистр, убираем обращения, а затем снова применяем TRIM — на случай, если после удаления обращения остался ведущий пробел. Каждый шаг — это одна функция, а сложность возникает только из-за их вложенности. Если стопка разрослась до четырёх и больше уровней — это уже сигнал: либо завести вычисляемую колонку (см. главу «Продвинутые возможности»), либо очищать данные ещё на этапе импорта.

Что стоит запомнить

• || склеивает строки; NULL отравляет результат, поэтому подстраховывайтесь через COALESCE.
• Связка SUBSTR и INSTR закрывает большинство задач в духе «найди и отрежь».
• REPLACE заменяет все вхождения указанной подстроки.
• TRIM и его собратья умеют работать не только с пробелами — им можно передать свой набор символов.
• printf — правильный инструмент, когда нужен форматированный вывод.
• LIKE — это SQL-шаблоны без учёта регистра, GLOB — шаблоны в стиле шелла с учётом регистра.

Дальше: числовые функции

Со строками разобрались — логично перейти к числам: округление, модуль, особенности деления и математические функции, которые появились в SQLite в последних версиях. Об этом — на следующей странице.