半角と全角スペースを削除または回避するSQL

空欄なのに半角と全角のデータが間違って入ってしまうことがあります。
そのままで困らないこともありますが、SELECT文で空白を取得したいのにスペースのせいで取得できないことがあります。そのためあらかじめスペースを削除する（データクレンジング）SQLを用意するか、SELECT文で取得する時にスペースを変換してあげる必要があります。
今回はまとめて削除、変換方法を紹介したいと思います。

動作環境

この記事はSQL FIDDLEでMySQL5.6、PostgreSQL9.6、SQLiteで動作確認しました。
下記文章は主にPostgreSQLで仕様で調べています。

半角と全角スペースを削除／変換する方法

以下を使用半角と全角スペースを削除／変換していきます。

trim(replace(カラム名, ‘　’, ‘ ‘))

–わかりづらいので文字で書きます
trim(replace(カラム名, ‘全角スペース’, ‘半角スペース ‘))

trim()は全角スペースを削除してくれないので、全角スペースを半角スペースにreplace()で変換してからtrimしています。

またtranslate()を使用する方法もあります。

trim(translate(space, ‘　’, ‘ ‘))

細かくやると違いはあるかもしれませんが以下の作業は代替可能です。

大きな違いは使える環境によります。

DB	REPLACE	TRANSLATE
Access	○	×
SQLServer	○	×
Oracle	○	○
MySQL	×	○
PostgreSQL	○	○

準備

以下の内容のテーブルを使用します。

itemid	itemname	space
1	空白
2	半角スペース
3	全角スペース(Webだと入らないから■で代用)	■
4	半角スペース複数
5	全角スペース複数(Webだと入らないから■で代用)	■■■■
6	半角スペース複数ひらがなあり	あ い う
7	全角スペース複数ひらがなあり(Webだと入らないから■で代用)	■あ■い■

上記テーブルの作成用コードです。

— CREATE文 item テーブル
CREATE TABLE item (
ItemID numeric(8, 0) unique not null,
ItemName character varying (30),
SPACE character varying (20),
primary key(ItemID)
);
— itemテーブル用INSERT文
insert into item
values(0001, ‘空白’, ”),
(0002, ‘半角スペース’, ‘ ‘),
(0003, ‘全角スペース(Webだと入らないから■で代用全角に直してください)’, ‘■’),
(0004, ‘半角スペース複数’, ‘ ‘),
(0005, ‘全角スペース複数(Webだと入らないから■で代用)’, ‘■■■■’);

半角と全角スペースを「削除」する方法

削除と書きましたが、半角スペースor全角スペースを「”」で置換する方法です。

UPDATE item set space = trim(replace(space, ‘■’, ‘ ‘));

上記を実行することで全角を半角スペースを消すことができます。

※■は全角スペースの変わりで使用しています
半角スペースもWebだと自動で消えてしまうのですがローカル環境で確認済みなので許して

実行後テーブル

itemid	itemname	space
1	空白
2	半角スペース
3	全角スペース(Webだと入らないから■で代用)
4	半角スペース複数
5	全角スペース複数(Webだと入らないから■で代用)
6	半角スペース複数ひらがなあり	あ い う
7	全角スペース複数ひらがなあり(Webだと入らないから■で代用)	あ い

Trimは前後のみに実行され文字間には適用されません。

半角と全角スペースを「変換」する方法

SELECT文で一時的に半角スペース、全角スペースを変換して使う方法です。

Select
    ItemName
    ,space
from
(SELECT
    itemname
    ,trim(replace(space, ‘■’, ‘ ‘)) as space
from
    item
)trimTable
where
    Space = ”

trimとreplaceで変換した直後のwhere句でSpace = ” をしても、変換前の値を使用されてしまうのでサブクエリとして使用してそのあとに取得します。

※■は全角スペースの変わりで使用しています

実行結果

itemname	space
空白
半角スペース
全角スペース(Webだと入らないから■で代用)
半角スペース複数
全角スペース複数(Webだと入らないから■で代用)

おわり

いろいろ実験してみると面白いと思います。

ゴミデータを消すまたは正常の値に戻すことをデータクレンジングっていうらしいです。
データクレンジングがらみでいっぱい書けそうです。