皆様こんにちは
唐突ですが、Excelで全角の英数字と半角が混じったカラムに悩まされた事はありましたか？
勿論Excelに限らずデータベースでも全角と半角が混じったデータが入っていたおかげで
DISTINCT(重複の除外)をしても正しい結果が入ってこなかった等といったことがあるかと思います。
今日は全角英数字を半角英数字に、全角カタカナを半角カタカナに、する方法を学んでみたいと思います。
また併せてこの変換ロジックをUDFにしてしまおうという回になります。
それではよろしくおねがいします。
1.変換用のSQLはセットで覚えておく
非常に複雑かつ、カラム内には数字もあれば英字もカタカナも混在していることがあります。
一緒に変換してしまうのが手っ取り早いのでまとめて処理してしまいましょう
WITH
DEMO AS (
SELECT
“Ｓｓ１２” AS TEKITOU
)
全角が半角になるかわかりやすく分けたので次に変換するためのSQLを繋げます
SELECT
TEKITOU,
–ここから変換処理用のクエリ
CODE_POINTS_TO_STRING(
ARRAY(
SELECT
CASE
— ↓全角の大文字を半角の大文字に変換 ＨＯＧＥＨＯＧＥ → HOGEHOGE
WHEN MORE_TEKITOU BETWEEN 65313 AND 65338 THEN MORE_TEKITOU-65248
— ↓全角の小文字を半角の小文字に変換 ｈｙｈｙ → hyhy
WHEN MORE_TEKITOU BETWEEN 65345 AND 65370 THEN MORE_TEKITOU-65248
— ↓全角の数字を半角の数字に変換 １２ → 12
WHEN MORE_TEKITOU BETWEEN 65296 AND 65305 THEN MORE_TEKITOU-65248
ELSE MORE_TEKITOU END
FROM
— TO_CODE_POINTSという引数に半角変換を行いたい列を指定する
UNNEST( TO_CODE_POINTS(TEKITOU)) AS MORE_TEKITOU
)
)
–ここまで変換処理用のクエリ
FROM
DEMO
2.全角から半角に変更するクエリ部分をUDFに落とし込む
変換することができましたのでこれをSQLに直接落とし込むのではなく
ユーザー定義関数(UDF)にしていつでも呼び出し可能な状態にします。
CREATE OR REPLACE FUNCTION
`BQを使ってるプロジェクト名.データセット名.作成時のUDF関数テーブル名` –あとでなおす
(TEKITOUNA_KARAMUMEI STRING) AS (
CODE_POINTS_TO_STRING(
ARRAY(
SELECT
CASE
— ↓全角の大文字を半角の大文字に変換 ＨＯＧＥＨＯＧＥ → HOGEHOGE
WHEN MOTTO_TEKITOU BETWEEN 65313 AND 65338 THEN MOTTO_TEKITOU-65248
— ↓全角の小文字を半角の小文字に変換 ｈｙｈｙ → hyhy
WHEN MOTTO_TEKITOU BETWEEN 65345 AND 65370 THEN MOTTO_TEKITOU-65248
— ↓全角の数字を半角の数字に変換 １２ → 12
WHEN MOTTO_TEKITOU BETWEEN 65296 AND 65305 THEN MOTTO_TEKITOU-65248
ELSE MOTTO_TEKITOU END
FROM
— TO_CODE_POINTSという引数に半角変換を行いたい列を指定する
UNNEST( TO_CODE_POINTS(TEKITOUNA_KARAMUMEI)) AS MOTTO_TEKITOU
)
)
–ここまで変換処理用のクエリ
);
続いて実験用のテーブルを作成します。
— テーブル作成時にカラムを作ります
CREATE OR REPLACE TABLE
`BQを使ってるプロジェクト名.作成したいデータセット名.作成したいテーブル名` (
DEMO1 STRING,
DEMO2 STRING,
);
— 作成したテーブルに値を設定していく
INSERT INTO
`BQを使ってるプロジェクト名.作成したいデータセット名.作成したいテーブル名`
(DEMO1, DEMO2)
VALUES
(“２０２１”, “ＲＥｉＷａ”),
(“2021”, “ReIwA”),
(“００７9”, NULL),
(“0079”, “”)
実際に関数が動くか確認してみましょう。
SELECT
DEMO1,
BQを使ってるプロジェクト名.データセット名.本項で作成したUDF関数名(DEMO1),
DEMO2,
BQを使ってるプロジェクト名.データセット名.本項で作成したUDF関数名(DEMO2),
FROM
`BQを使ってるプロジェクト名.データセット名.テーブル名`
関数自体は問題なく動作しているようですがDEMO2のNULLと空白をUDF関数で
変換したところNULLではなく空白になってしまいました。
NULLにしたいので解決方法の1例として下記のように組合せして解決可能です。
SELECT
DEMO1,
BQを使ってるプロジェクト名.データセット名.本項で作成したUDF関数名(DEMO1),
DEMO2,
CASE
WHEN DEMO2 = “” THEN NULL
WHEN DEMO2 IS NULL THEN NULL
ELSE BQを使ってるプロジェクト名.データセット名.本項で作成したUDF関数名(DEMO2)
END
FROM
`BQを使ってるプロジェクト名.データセット名.テーブル名`
いかがでしたでしょうか。
次回はUDF関数で列と行を置換えるのを考えます。
ご覧いただきましたありがとうございました。
コメントを残す