Unicode コードポイントの扱い
PostgreSQL を業務で扱う中で遭遇した「Unicode コードポイントの扱い」について、実務で役立つ内容をまとめました。
Oracle から PostgreSQL へ移行する際に混同しやすいポイントについても併せて紹介します。
各種コードは以下で試すなどしてみてください
https://sqliteonline.com/
1. Unicode コードポイント変換（文字 ⇔ コードポイント）
■ なぜ必要になるのか
- 文字化けの原因を調査したい
- アプリケーションと PostgreSQL で Unicode の扱いを確認したい
- ログや監査データに含まれる文字のコードポイントを確認したい
Python では ord() と chr() を利用しますが、PostgreSQL では ascii() と chr() を利用できます。
これにより無限に特殊文字の変換部分で悩まされました。
■ 文字 → Unicode コードポイント
実行例
SELECT
'西' AS character,
ascii('西') AS codepoint;
実行結果
| character | codepoint |
|---|---|
| 西 | 35199 |
解説
UTF-8 データベースでは、ascii() は文字列の先頭文字の Unicode コードポイントを返します。
SELECT ascii('A');
-- 65
SELECT ascii('あ');
-- 12354
SELECT ascii('西');
-- 35199
注意
ascii()が対象とするのは先頭文字のみです。
したがってSELECT ascii('西山');とした場合も35199が返ります。
文字列全体のコードポイントを取得したい場合は、文字単位に分割して処理する必要があります。
■ Unicode コードポイント → 文字
実行例
SELECT chr(35199);
実行結果
| chr |
|---|
| 西 |
解説
chr(integer) は Unicode コードポイントから文字を生成します。
SELECT chr(65);
-- A
SELECT chr(12354);
-- あ
SELECT chr(35199);
-- 西
■ UTF-8 バイト列を確認したい場合
コードポイントではなく、実際に保存される UTF-8 のバイト列を確認したい場合は convert_to() と encode() を利用します。
SELECT
encode(convert_to('西', 'UTF8'), 'hex');
実行結果
e8a5bf
これは UTF-8 のバイト列であり、
Unicode
U+897F
と
UTF-8
E8 A5 BFはノットイコールです
コードポイントと UTF-8 のバイト列は異なる概念なので、混同すると後で苦労します。
2. コードポイントを利用して文字を変換する
コードポイントが分かれば、chr() を利用して任意の文字を生成できます。
ここでは例として、
西
↓
覀
へ変換してみます。
■ まずはコードポイントを確認する
SELECT
ascii('西') AS Before,
ascii('覀') AS After;
実行結果
| Before | After |
|---|---|
| 35199 | 35200 |
つまり、
| 文字 | Unicode |
|---|---|
| 西 | U+897F |
| 覀 | U+8980 |
となります。
■ chr() を利用して文字を生成する
コードポイントが分かれば、chr() で文字を生成できます。
SELECT chr(35200);
実行結果
| chr |
|---|
| 覀 |
■ replace() と組み合わせる
実際の業務では、文字列中の特定の文字を別の文字へ置き換えたいケースが多くあります。
例えば、「JR西日本」の「西」を「覀」に置き換える場合は以下のように記述できます。
SELECT replace('JR西日本', '西', chr(35200));
実行結果
| replace |
|---|
| JR覀日本 |
chr() と replace() を組み合わせることで、コードポイントから生成した文字をそのまま文字列の置換に利用できます。
にしてもこの表記だとちょっと気持ち悪いですね。
■ 変換前後のコードポイントを確認する
変換前後の文字とコードポイントを同時に確認してみます。
SELECT
'西' AS before_char,
ascii('西') AS before_codepoint,
chr(35200) AS after_char,
ascii(chr(35200)) AS after_codepoint;
実行結果
| before_char | before_codepoint | after_char | after_codepoint |
|---|---|---|---|
| 西 | 35199 | 覀 | 35200 |
このように、ascii() と chr() を組み合わせることで、コードポイントを確認しながら安全に文字を変換できます。
3. 実務で役立つ場面
コードポイントを扱えるようになると、次のような場面で役立ちます。
- 文字化けの原因調査
- Unicode の異体字対応
- 特殊文字の変換
- システム間での文字コード比較
- ログやCSVデータに含まれる特殊文字の解析
一見すると地味な機能ですが、人の名前を扱ったりする場合
確実に必要な要件になります。
人の名前の細かな違いを是としない風潮があるため
（「吉」と「𠮷」なんかもひとまずこういったシステム上は「吉」になるものみたいな
認識を持ってもらえたらとても楽なのになといつも思います）
日本でプログラマをしていると必ずぶち当たる問題なので
今後脱オラクルが進む今覚えておいて損はないかなと思います