SQLで半角文字⇒全角変換し特定の文字列を取りたい

質問

以前もSQLで出来ないか似たような質問をして、頂いた回答で出来るかとと思いましたが、質問の内容では足りないことが分かり再度質問致します。
テーブルA(置き換えをかけたい文字列が入っています)
項目名：A_Z
1件目：（株）あｶあｲa　
2件目：いｹ"いｺ"い（学）　
3件目：う（有）う（株）
テーブルB（置き換える文字列が入っています)
項目名： B_A 　　 B_B
　　　　（株）　　なし
　　　　（有）　　なし
　　　　　ｶ　　　　カ
　　　　　ｹ"　　　 ゲ
　　　　　a ａ
処理結果
1件目：あカあｲａ　
2件目：いゲいｺ"い（学）　
3件目：うう
SQLでテーブルAの文字列をテーブルBの項目B_Aの内容に該当する文字列をテーブルBの項目B_Bに置き換えを行い、テーブルAを更新(UPDATE)したいです。(テーブルBの項目B_B部分の登録内容「なし」は置き換えではなく取り除きを行いたいものです。登録は '' で登録しています。)
簡潔に言うと、テーブルAの内容の半角文字を全角変換し、特定文字列を取り除く処理を行いたいです。
取り除きたい文字列は、1つのレコード上に2つ以上あるパターンもあります。(テーブルAの3件のように)
初心者の為、どう書いたらよいのか全く分からず困っております。
出来ればMySQLとSQL Serverでの書き方ををご教授願います。

回答No.8

dell_OK · ベストアンサー

「1.どうしてtable_a(更新したいデータが入っているテーブル)を1件も読んでいない(FETCHしていない)のに全てのtable_aのデータが更新されるのでしょうか？」

UPDATEをストアド内に記述していますが、回答No.6で実行していただいたのと同じで、単純なUPDATEでtable_aを更新しています。
無条件と言ったのは、WHEREなしで全レコードを対象にする、と言う意味です。
回答No.1のストアドでは、最初に質問された時の、table_aを１件ずつtable_bの件数分処理する、ようにしたので、現在読んでいる１件をidで指定する必要がありました。

FETCHはCURSORに基づいてレコードを読むためのものなので、更新に必要なものではありませんし、CURSORは読み取り専用です。

「2.ストアドプロシジャーを書いていてよく起きたエラーなのですが、IFやCASEのTHEN(ELSEも同じ)には1行しか記載出来ないのでしょうか？」

命令ひとつずつにデリミタ（通常はセミコロン）を入れてみてください。
IF XXXX
THEN SET AAA = 'AAA';
SET BBB = 'BBB';

お礼

丁寧なご回答いつもありがとうございます。
分かりやすくとても助かりました。

回答No.7

dell_OK

ＳＱＬでは置換できたようですので、ＵＰＤＡＴＥで直接するようにしてみました。
テーブルＢだけをもとにループして、テーブルＡを無条件にＵＰＤＡＴＥします。
質問者さまの方でこれがうまくいくとも言えませんので、環境による差異があるのかも知れません。
うまくいかなかった場合は、データベースやテーブルの照合順序、文字列項目のデータ型と長さを教えてください。
ちなみに私の環境は、照合順序はutf8mb4_general_ciで、データ型はtext（長さはありません）にしています。

BEGIN
DECLARE done int DEFAULT FALSE;
DECLARE b_a_text text;
DECLARE b_b_text text;
DECLARE cur_b CURSOR FOR
SELECT B_A,B_B FROM table_b ORDER BY LENGTH(B_A) DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET
done = TRUE;
OPEN cur_b;
read_loop_b:LOOP
FETCH cur_b INTO b_a_text,b_b_text;
IF done THEN
LEAVE read_loop_b;
END IF;
UPDATE table_a SET A_Z = REPLACE(A_Z,b_a_text,b_b_text);
END LOOP;
CLOSE cur_b;
END

補足

ご回答いただきありがとうございます。
この書き方ですと全て更新がかかりました。

すみませんが以下質問させてください。
1.どうしてtable_a(更新したいデータが入っているテーブル)を1件も読んでいない(FETCHしていない)のに全てのtable_aのデータが更新されるのでしょうか？
2.ストアドプロシジャーを書いていてよく起きたエラーなのですが、IFやCASEのTHEN(ELSEも同じ)には1行しか記載出来ないのでしょうか？
IF XXXX
THEN SET AAA = 'AAA'
SET BBB = 'BBB'
のようにTHENの中に複数の文を書くと、エラーになってしまいます。
(素人の質問で申し訳ありません)

回答No.6

dell_OK

なんだか不思議な状態ですね。
では、このＳＱＬを実行してみていただけますか。
UPDATE TABLE_A SET A_Z = REPLACE (A_Z, 'ｶ', 'カ');
これで半角「ｶ」が全角「カ」に置換されるかどうか。

補足

お手数をお掛けして申し訳ありません。
UPDATE TABLE_A SET A_Z = REPLACE (A_Z, 'ｶ', 'カ');
を実行したところ、
（株）あカあｲa
になりました。

回答No.5

dell_OK

半角の円マークは２個書いてみてください。
INSERT INTO TABLE_B(B_A,B_B) VALUE('\\', '￥');

回答No.4

dell_OK

そうでしたか。
もしかしたら文字コードのせいかと思って、テーブルＡとテーブルＢの照合順序をＵＴＦ８とシフトＪＩＳで別々にして試してみたのですが、変換されてしまったので、文字コードは関係なさそうですね。
アルファベットも追加してみてどうなるか確認できますでしょうか。
あと、B_Aに「ｶ」と見えていて「ｶ 」のように空白が付いていないでしょうか。

補足

ご回答ありがとうございます。
登録はこんな感じで書いています。
INSERT INTO TABLE_B(B_A,B_B) VALUE('aﾞ','ａ');
INSERT INTO TABLE_B(B_A,B_B) VALUE('ｶﾞ', 'ガ');
余計なスペースなどは入っていないと思います。
よく分らないのが、（株）などは '' (取り除き)出来るのですが、半角カタカナ(もしくは記号？)が混在している置き換え(ｶ ⇒ カなど)が出来ないような感じです。半角カタカナ(もしくは記号？)が混在していない場合は英文字の変換は出来ますが、混在していると見た目は正常に処理されますが、変換が行われていません。

※上記内容とは関係ないと思いますが、円マーク(￥)を登録しようとするとエラーになるのは仕様なのでしょうか？

回答No.3

dell_OK

私の方では変換されました。
テーブルＢに「ｶ：カ」「a：ａ」があるので、他の半角文字もすべて登録されているのかと思っていましたが、そうではないのでしょうか。
半角文字は英数カタカナで１５０文字程度あると思いますのですべて登録してください。
テーブルＢのようなものを使用しないで半角全角変換する機能を持っているデータベースはたぶんなかったと思います。

別の方法で変換することもできます。
例えば、ＥｘｃｅｌやＡｃｃｅｓｓのＶＢＡに全角変換する機能があるので、ＯＤＢＣを使ってデータベースに接続して、全角変換と特定文字の削除をして更新する方法です。
こちらの方法がよろしければそれを案内させていただきます。

補足

テーブルBをカタカナのみにしてもやっぱり変わりません。
変更をかける文字列(テーブルA)
　A_Z：アセットﾏﾈｼﾞﾒﾝﾄーOne・
本来であれば結果として、
　アセットマネジメントーOne・
となるはずですが、変換前と同じ結果です。
Query OK, 0 rows affected (0.00 sec)　と返ってきますが。
テーブルAまたはテーブルBの登録内容がおかしいのでしょうか？
ちなみにカタカナだけ登録したため、テーブルBは全部で84文字でした、(濁点付きや半角小文字('ｯ','ッ')も含みます)

回答No.2

dell_OK

書き忘れました。
テーブルＡには、プライマリーキーとなる「id」がある前提なので、UPDATEのWHEREで使える項目があれば、それに替えてください。

補足

ご回答いただきありがとうございます。
試してみたのですが、全角文字の置き換え（（株）を取るなど）は旨く動きますが、半角文字を全角に置き換え(カタカナ)が置き換わってくれないみたいです。

回答No.1

dell_OK

MySQLでストアドプロシージャーを作ってみました。

メモ
テーブルＢをB_Aの文字長の逆順で読んでいるのは、例えば、「ｹ"」→「ゲ」と「ｹ」→「ケ」があった場合に、先に「ｹ」が置換されると「ｹ"」が「ケ"」（ケと濁点）になってしまい一文字の「ゲ」にならなくなるため、なるべく文字数の多い方から置換した方が安全かと思ってのことです。

BEGIN
DECLARE done int DEFAULT FALSE;
DECLARE a_id int;
DECLARE a_z_text text;
DECLARE a_z_new_text text;
DECLARE b_a_text text;
DECLARE b_b_text text;
DECLARE cur_a CURSOR FOR
SELECT id,A_Z FROM table_a;
DECLARE cur_b CURSOR FOR
SELECT B_A,B_B FROM table_b ORDER BY LENGTH(B_A) DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET
done = TRUE;
OPEN cur_a;
read_loop_a: LOOP
FETCH cur_a INTO a_id,a_z_text;
SET a_z_new_text = a_z_text;
IF done THEN
LEAVE read_loop_a;
END IF;
OPEN cur_b;
read_loop_b:LOOP
FETCH cur_b INTO b_a_text,b_b_text;
IF done THEN
LEAVE read_loop_b;
END IF;
SET a_z_new_text = REPLACE(a_z_new_text,b_a_text,b_b_text);
END LOOP;
CLOSE cur_b;
IF a_z_text != a_z_new_text THEN
UPDATE table_a SET A_Z = a_z_new_text WHERE id = a_id;
END IF;
SET done = false;
END LOOP;
CLOSE cur_a;
END