帰ってきた！ 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw Tweet
先日、Facebookの思い出なんぞを見返していたら、子供が小さい頃、ROT13で、謎の文字列を解かせる、コナンくん遊びをしてたことを思い出した。
流石に小さい頃なので、難易度を下げるためROT13の文字対応表をヒントとして与えることにしようと思ったのですが、それだと逆に簡単になり過ぎると考え、文字列の順番を示す数字を、犯人からのメッセージとして、
謎の数字から犯人のメッセージを解け！。
みたいなことをやっていました。
意外と受けが良くて、複数の問題を解いて遊んで、問題を作る方が疲れるというわけわからん事態になってしまったことがありましたw
ということで、
本日の、帰ってきた！ 標準はあるにはあるが癖の多いSQL は、 ROT13やるにも、Oracle/PostgreSQL/MySQLの癖が出ますよ！。というお話。
(実装は、SQL script file化してパラメータ渡し、渡せない癖のあるものは、代替方法にて渡すことにします。また、SQLだけで解決することにします）
前置きはこれぐらいにして、早速、間違いない、Oracleからw
ROT13 cipher実装するのにベタな感じで、PL/SQLになどに逃げる必要はないです。非常に強力な関数、 translate() 一発で解決できますよ。
SCOTT@freepdb1> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
経過: 00:00:00.02
文字列をrot13/derot13するSQL scriptファイルです。
Oracleの場合、単純です。translate()で対応する文字を返すようにするだけです。
SCOTT@freepdb1> !cat rot13.sql
SELECT
TRANSLATE
(
'&1'
,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
,'NOPQRSTUVWXYZABCDEFGHIJKLMnopqrstuvwxyzabcdefghijklm'
)
;
UNDEFINE 1
SCOTT@freepdb1> !cat derot13.sql
SELECT
TRANSLATE
(
'&1'
,'NOPQRSTUVWXYZABCDEFGHIJKLMnopqrstuvwxyzabcdefghijklm'
,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')
;
UNDEFINE 1
では、実行！ うまく行った！
簡単でしょ！この手のSQL文でのお遊びは。
SCOTT@freepdb1> @rot13 'Mac De Oracle'
TRANSLATE('MA
-------------
Znp Qr Benpyr
経過: 00:00:00.00
SCOTT@freepdb1> @derot13 'Znp Qr Benpyr'
TRANSLATE('ZN
-------------
Mac De Oracle
経過: 00:00:00.01
つづいで、PostgreSQLです。
perftestdb=> select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
(1 行)
実行！
[postgres@localhost ~]$ psql -d perftestdb -U discus -p 5432 -W -h localhost -f /var/lib/pgsql/pg_rot13.sql -v str="'Mac De Oracle'"
translate
---------------
Znp Qr Benpyr
(1 行)
[postgres@localhost ~]$ psql -d perftestdb -U discus -p 5432 -W -h localhost -f /var/lib/pgsql/pg_derot13.sql -v str="'Znp Qr Benpyr'"
translate
---------------
Mac De Oracle
(1 行)
PostgreSQLの場合、translate()関数が実装されており、Oracleと同じように利用できました。SQL scriptファイル実行時のパラメータの渡し方に癖がある感じですかね。（なかなか、慣れないです。これ）
[postgres@localhost ~]$ cat pg_rot13.sql
SELECT
TRANSLATE
(
:str
,'NOPQRSTUVWXYZABCDEFGHIJKLMnopqrstuvwxyzabcdefghijklm'
,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')
;
[postgres@localhost ~]$ cat pg_derot13.sql
SELECT
TRANSLATE
(
':str'
,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
,'NOPQRSTUVWXYZABCDEFGHIJKLMnopqrstuvwxyzabcdefghijklm')
;
さて、最後は、MySQL 8.0.xを利用します。理由は使える構文が多くなったためですが。おそらくそれ以前のMySQLだと実装はかなり辛い。
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32 |
+-----------+
1 row in set (0.00 sec)
理由は、見ての通り、再帰問合せを利用する必要があるためです。これ使えないとかなりキツイ。w
ついでに、translate()関数も存在しないため、バリバリ既存の関数を使ってシンプルに書いても多分これぐらいw
他に、もっといいやり方あるよーって方、ブログ書いて公開してほしいっす！
ちなみに、ググったら、こんなのをGitHubで見つけました。同じ方法じゃなくてよかったという感じではあります :) 俺オリジナルだ:)
こちらの方は、function化したようですね
https://github.com/samuelfaj/MySQL-rot13/blob/master/MySQL_rot13.sql
では、私が作ったSQL scriptファイルでパラメータをSETコマンドで渡す方法。苦心の跡が見えるかと思います。（これは無理だ、function化して逃げるかー。と一瞬過りましたが、閃いた方法がよかったという感じ）
mysql> \! cat mysql_rot13.sql
WITH RECURSIVE rot13(v)
AS
(
SELECT 1
UNION ALL
SELECT v + 1
FROM
rot13
WHERE v + 1 <= CHAR_LENGTH(@str)
)
SELECT
GROUP_CONCAT(
CASE
WHEN
INSTR(
CAST('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' AS BINARY)
, SUBSTR(@str, v, 1)
) = 0
THEN
SUBSTR(@str, v, 1)
ELSE
SUBSTR(
'NOPQRSTUVWXYZABCDEFGHIJKLMnopqrstuvwxyzabcdefghijklm'
, INSTR(
CAST('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyza' AS BINARY)
, SUBSTR(@str, v, 1)
)
, 1
)
END
SEPARATOR ''
) AS rot13
FROM rot13
;
mysql> \! cat mysql_derot13.sql
WITH RECURSIVE derot13(v)
AS
(
SELECT 1
UNION ALL
SELECT v + 1
FROM
derot13
WHERE v + 1 <= CHAR_LENGTH(@str)
)
SELECT
GROUP_CONCAT(
CASE
WHEN
INSTR(
CAST('NOPQRSTUVWXYZABCDEFGHIJKLMnopqrstuvwxyzabcdefghijklm' AS BINARY)
, SUBSTR(@str, v, 1)
) = 0
THEN
SUBSTR(@str, v, 1)
ELSE
SUBSTR(
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
, INSTR(
CAST('NOPQRSTUVWXYZABCDEFGHIJKLMnopqrstuvwxyzabcdefghijklm' AS BINARY)
, SUBSTR(@str, v, 1)
)
, 1
)
END
SEPARATOR ''
) AS derot13
FROM derot13
;
では実行。
mysql> set @str='Mac De Oracle';
Query OK, 0 rows affected (0.01 sec)
mysql> \. mysql_rot13.sql
+---------------+
| rot13 |
+---------------+
| Znp Qr Benpyr |
+---------------+
1 row in set (0.00 sec)
mysql> set @str='Znp Qr Benpyr';
Query OK, 0 rows affected (0.00 sec)
mysql> \. mysql_derot13.sql
+---------------+
| derot13 |
+---------------+
| Mac De Oracle |
+---------------+
1 row in set (0.00 sec)
ということで、いろいろな、癖という鞭に打たれても、ニッコリ笑える。変態になれると良いですね :)
まだまだまだ、クソ暑い、東京からお送りしました。 ではまた。
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠（有名なやつ）
・標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
・標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
・標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
・標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
・標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか？
・標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
・標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
・標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
・標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
| 固定リンク | 0
コメント