SQLiteでアクセント付き文字列を正しくソートする方法：完全ガイド

2024-06-25

SQLite でアクセント付き文字列の列をソートする方法

COLLATE を使用する

最も一般的な方法は、COLLATE キーワードを使用して、列のソート順序を指定することです。 COLLATE には、さまざまなロケールやソートアルゴリズムを指定できます。

例：

CREATE TABLE users (
  name TEXT COLLATE 'unicode'
);

この例では、name 列は unicode ロケールでソートされます。 unicode ロケールは、Unicode 標準に従って文字列をソートします。

NORMALIZE を使用する

NORMALIZE 関数を使用して、文字列を正規化することもできます。正規化は、アクセント付き文字などの特殊文字を、より一般的な形式に変換するプロセスです。

SELECT name FROM users ORDER BY NORMALIZE(name);

この例では、name 列は、正規化された文字列に基づいてソートされます。

カスタム照合関数を使用する

より高度なソート順序が必要な場合は、カスタム照合関数を作成できます。カスタム照合関数は、文字列をソートする方法を定義する独自のコードです。

CREATE FUNCTION my_collate(text)
RETURNS TEXT
AS
BEGIN
  RETURN LOWER(NORMALIZE(text, 'NFD'));
END;

CREATE TABLE users (
  name TEXT COLLATE my_collate
);

SELECT name FROM users ORDER BY name;

この例では、my_collate というカスタム照合関数が作成されます。この関数は、文字列を小文字に変換し、正規化してから返します。 name 列は、このカスタム照合関数を使用してソートされます。

CREATE TABLE users (
  name TEXT COLLATE 'unicode'
);

INSERT INTO users (name) VALUES ('José');
INSERT INTO users (name) VALUES ('João');
INSERT INTO users (name) VALUES ('Pierre');

SELECT * FROM users ORDER BY name;

このコードは、次の結果を出力します。

name
-----
João
José
Pierre

CREATE TABLE users (
  name TEXT
);

INSERT INTO users (name) VALUES ('José');
INSERT INTO users (name) VALUES ('João');
INSERT INTO users (name) VALUES ('Pierre');

SELECT * FROM users ORDER BY NORMALIZE(name);

name
-----
João
José
Pierre

CREATE FUNCTION my_collate(text)
RETURNS TEXT
AS
BEGIN
  RETURN LOWER(NORMALIZE(text, 'NFD'));
END;

CREATE TABLE users (
  name TEXT COLLATE my_collate
);

INSERT INTO users (name) VALUES ('José');
INSERT INTO users (name) VALUES ('João');
INSERT INTO users (name) VALUES ('Pierre');

SELECT * FROM users ORDER BY name;

name
-----
joão
josé
pierre

説明

上記のコードは、SQLite データベースを作成し、users という名前のテーブルを作成します。
users テーブルには、name という名前の列があります。
name 列のデータ型は TEXT です。
COLLATE キーワードを使用して、name 列のソート順序を指定します。
NORMALIZE 関数を使用して、文字列を正規化します。
my_collate というカスタム照合関数が作成されます。
my_collate 関数は、文字列を小文字に変換し、正規化してから返します。
ORDER BY 句を使用して、結果をソートします。

注意事項

上記のコードはあくまで例であり、ニーズに合わせて変更する必要があります。
SQLite のバージョンによっては、機能が異なる場合があります。
詳細については、SQLite のドキュメントを参照してください。

SQLite でアクセント付き文字列の列をソートするその他の方法

ICU 拡張機能を使用する

ICU (International Components for Unicode) 拡張機能は、Unicode 文字列の処理を追加機能を提供します。 ICU 拡張機能を使用すると、ロケール固有のソート順序を使用して文字列をソートすることができます。

ENABLE_LOAD_EXTENSION( 'icu' );

CREATE TABLE users (
  name TEXT COLLATE 'icu'
);

INSERT INTO users (name) VALUES ('José');
INSERT INTO users (name) VALUES ('João');
INSERT INTO users (name) VALUES ('Pierre');

SELECT * FROM users ORDER BY name;

この例では、icu 拡張機能が有効化され、name 列は icu ロケールでソートされます。

FTS5 フルテキスト検索モジュールは、全文検索機能に加えて、アクセント付き文字列のソート機能も提供します。

CREATE TABLE users (
  name TEXT
);

INSERT INTO users (name) VALUES ('José');
INSERT INTO users (name) VALUES ('João');
INSERT INTO users (name) VALUES ('Pierre');

ATTACH DATABASE 'fts5.db' AS fts;
CREATE VIRTUAL TABLE users_fts5 USING fts5(
  tokenize = 'unicode61'
);
INSERT INTO users_fts5 SELECT * FROM users;

SELECT * FROM fts.users_fts5 ORDER BY name;

この例では、fts5.db という名前の仮想データベースが作成され、users_fts5 という名前の仮想テーブルが作成されます。 tokenize オプションは、unicode61 ロケールを使用して文字列をトークン化することを指定します。 ORDER BY 句を使用して、name 列に基づいて結果をソートします。

外部ライブラリを使用する

SQLite には、アクセント付き文字列のソートを支援する外部ライブラリがいくつかあります。

使用する方法は、ニーズによって異なります。