SQLite 文字列関数まとめ：SUBSTR・REPLACE・INSTR

実務のクエリは、ほとんどが文字列との戦い

数値の扱いは簡単です。やっかいなのは文字列のほう ―― 余計な空白が混ざった名前、大文字小文字がバラバラのメールアドレス、ハイフンでつながったID、ほぼ一致しているのに微妙にズレているフリーテキスト項目など、現場では悩みの種が尽きません。SQLite には、こうしたケースをアプリ側のコードに頼らず処理できる、シンプルで実用的な文字列関数がひととおり用意されています。

このページでは、まず押さえておきたい sqlite 文字列関数として、連結・部分文字列の取り出し・検索・置換・トリム・整形を順に見ていきます。

sqlite の文字列連結は || 演算子で行う

SQLite には CONCAT 関数はありません。文字列をつなぐには || 演算子を使います。

SELECT 'こんにちは、' || '世界!' AS greeting;

SELECT 'ユーザー #' || 42 || ' が参加しました' AS message;

数値などほかの型は自動的にテキストに変換されます。ただし注意点として、オペランドのいずれかが NULL だと、式全体が NULL になります。これはSQL標準の挙動ですが、初見でハマる人が多いポイントです。

SELECT 'こんにちは、' || NULL || '!' AS greeting;

SELECT 'こんにちは、' || COALESCE(NULL, '友達') || '!' AS safe_greeting;

NULL を含む可能性のあるカラムは COALESCE(col, '') や COALESCE(col, 'default') で包んでおきましょう。そうしないと、値が一つ欠けるだけで文字列全体が NULL になってしまいます。

length・upper・lower で文字数や大文字小文字を扱う

この3つは sqlite 文字列関数の中でも特に出番が多いです。

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 で sqlite の部分文字列を取り出す

SUBSTR(text, start, length) は文字列の一部を切り出す sqlite の部分文字列関数です。インデックスは 1 始まりで、最初の文字は 0 ではなく 1 になる点に注意してください:

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

いくつか押さえておきたいポイントがあります。

• 第3引数は省略可能です。省略した場合は start から末尾までが返ります。
• start を負の値にすると、文字列の末尾から数えます。
• start が文字列の長さを超えていてもエラーにはならず、空文字列が返ります。

別のデータベースから来た人の指の記憶に配慮してか、SUBSTRING も同義語として使えます。

INSTR で部分文字列の位置を調べる

INSTR(haystack, needle) は、haystack の中で needle が最初に現れる位置を 1 始まりで返します。見つからなければ 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 を返すので、切り出す前に必ずチェックしてください。SUBSTR に 0 を渡すと、エラーにはならないものの想定外の結果になります。

REPLACE 関数で部分文字列を置き換える

REPLACE(text, old, new) は、old に一致する箇所を すべて new に置き換えます。

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

大文字と小文字は区別され、正規表現は使えません。あくまでリテラルな部分文字列だけです。複雑な変換が必要なときは REPLACE を入れ子にしてつなげることもできますが、2〜3段を超えてきたらアプリケーション側で処理した方がいいでしょう。

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;

既定では半角スペースを削除します。第2引数を指定すると、削る文字を自分で決められますが、ここで渡した文字列は「部分文字列」ではなく「削除対象の文字集合」として扱われる点に注意してください。たとえば TRIM('xxxhelloxx', 'x') の結果は 'hello' になります。

printf：数値と文字列のフォーマット

小数点以下の桁数を揃えたい、数値をゼロ埋めしたい、16進数で出力したい——そんなときに使えるのが printf（別名 format）です。

SELECT
    printf('%.2f',         3.14159)            AS two_decimals,
    printf('%05d',         42)                 AS padded,
    printf('$%,.2f',       1234567.89)         AS money,
    printf('%s は %d 歳です',     'エイダ',  30)         AS sentence;

書式指定子は C 言語の流儀に従っていて、%d、%s、%f、%x、0 やスペースでのパディングといった指定がそのまま使えます。|| と大量の CAST を組み合わせて文字列を組み立てるより、はるかにスッキリ書けます。

sqlite の like と glob の違い：パターンマッチング

同じパターンマッチングでも、この2つの演算子はまったく別物です。

LIKE は SQL でおなじみのワイルドカード、つまり任意の文字列にマッチする % と1文字にマッチする _ を使います。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 シェル風のワイルドカードを使います。具体的には、任意の文字列にマッチする *、1 文字にマッチする ?、文字クラスを表す [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%'）に限られます。先頭にワイルドカードが来ると、フルスキャンになってしまいます。

文字列の分割：SPLIT 関数は存在しない

SQLite には SPLIT_STRING 関数がありません。実用的な回避策は次の2つです。

-- 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 配列に対して json_each を使うか、再帰 CTE を書くかのどちらかです。これは後の章で改めて扱うので、ここでは「全部の単語をくれ」が SQLite では一行で書けない、ということだけ覚えておいてください。

実践例：名前データのクリーニング

ここまでの内容を組み合わせてみましょう。users テーブルに表示名が雑に入っていて、余計な空白が混じっていたり、大文字小文字がバラバラだったり、"Dr. " や "Mr. " といった敬称を取り除きたい、というよくあるケースを考えます。

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;

式は内側から外側へと読み解きます。まず外側の空白を取り除き、小文字に変換し、敬称を削除して、敬称を消した結果として残った先頭の空白をもう一度トリムする、という流れです。それぞれのステップは単独の関数で、複雑さは積み重ねから生まれているだけです。ネストが3〜4段を超えてきたら、生成カラム(第「Advanced Features」章を参照)を使うか、データ取り込みの段階でクレンジングを済ませるサインだと考えてください。

この章のポイント

• 文字列の連結には || を使う。NULL が混ざると結果も NULL になるので COALESCE で守る。
• SUBSTR と INSTR を組み合わせれば、「探して切り出す」用途はほぼカバーできる。
• REPLACE は指定した部分文字列の出現箇所をすべて置換する。
• TRIM 系の関数は空白だけでなく、任意の文字セットを指定できる。
• フォーマット付き出力には printf が最適。
• 大文字小文字を区別しないSQLワイルドカード検索なら LIKE、シェル風で大文字小文字を区別したいなら GLOB。

次回:数値関数

文字列を一通り扱ったので、次は当然ながら数値の番です。丸め処理、絶対値、整数除算のクセ、そしてSQLiteの新しめのバージョンで追加された数学関数まで見ていきます。続きは次のページで。