まさ@ブログ書き込み中

まさ@ブログ書き込み中

まさの旅、英語、プログラミング、プライベートについて、色々記録しています。

SQL楽しい

 

こんばんは、座右の銘は「いっぱいいっぱい」のまさです。

 

最近SQLの勉強をTRYCODEさんで始めました。

オンラインで学べるプログラミング教育サービス「TRYCODE」

 

そこで今回は僕が少しハマったところを記録しておきたいと思います。

 

 

難しく考えてしまったやつ

クエリ文を考える問題で、LENGTHやSUBSTRING、REPLACEなどを始めとする基本的な関数を習った後の以下のような演習問題でハマりました。

会員のクレジットカード番号の一覧を、結果表の見出しが「カード会社ID」、「カード番号」となるように表示しなさい。その際、カード番号の末尾4ケタは「****」として隠すこと。

 

僕は

「こ、これは、今まで学んできた関数を複数使う応用問題だ!」

とわかりやすくひっかかりました。

 

当初のプランは

  • LENGTH(CARD_NUMBER)で各クレジットカード番号の桁数を把握
  • SUBSTRING FROM 桁数-4 FOR 4で最後の4桁の文字を切り出す
  • REPLACEでその4桁の文字と****を入れ替える

これを実現しようとすると

REPLACE(CARD_NUMBER, SUBSTRING FROM (LENGTH(CARD_NUMBER)-4) FOR 4, '****')

と果てしなく長く複雑なクエリ文になってしまいました。

 

しかも実行してみると、エラーが出る・・・。

関数の中に関数入れたらダメなのか?LENGTH()-4とか勝手に式を書いてはいけないのか?などと考えながら

 

データベースの定義書を見ると

CARD_NUMBERのデータ型はCHAR(19)とある

むむっ!?クレカの番号って普通16桁では・・・?

 

ググる「有名なクレカは16桁だけど全部のクレカが16桁とは限らない」とあったが、確かめるために実際にデータを出力させると、

0000-1111-2222-3333のように16桁とハイフン(-)で19文字としていたのです。

 

そこで僕は

SUBSTRING(文字列切り出し)で16文字目から4文字分文字を切り出しREPLACE文でその文字列を****に変えればいいじゃん、簡単だったなー

とか思いながら実行するも、それも失敗。

 

なぜなら、

1111-2222-3333-1111という番号の場合、1111を****に変えるため、

****-2222-3333-****となってしまうからです。

 

そこで気づいてしまいます。

最初の15文字だけSUBSTRINGで切り抜いて表示し、残りの文字列「****」をプラスするだけで良いんじゃないかということに。

 

具体的には以下のコードを書けばOK。

 

 

ひらめいたやつ

 

次の問題は「ある人の家族の末っ子の名前を表示しよう、子供がいなかったら『なし』と表示せよ」というもの。データは以下の通り。

f:id:masaincebu:20170821011024p:plain

 

child_name1~3の数字の部分は、大きければ大きいほど後から生まれたという意味になります。つまり、3までデータが埋まっていれば3が、2まで埋まっていれば2が、1しか埋まっていなければ1が末っ子の名前になります。

 

僕が習っていた関数として、COALESEという関数がありました。これは、NULLを返すものを別の文字列に変換できるというものです。

COALESE(NULL, 'This is NULL')のようにできます。

 

それぞれのCHILD_NAMEカラムにデータが入っているかどうかによって表示を変えるいう意味で、COALESEを使おう!と思ったのですが、(CHILD_NAME3, ‘なし’)とすると、child_name3がNULLかどうかしかチェックできないことに気づきました。

 

ここで、僕は「COALESE関数って引数を好きなだけ取れるのでは!?」と思いました。そこでこう書いてみると、

 

うまくいきました!

 

こう書けば、左(第一引数)から順に、NULLになれば次の引数が参照され、NULLにならなければその引数の値が表示されます。

 

つまりCHILD_NAME3から順にチェックされ、CHILD_NAMEが一つもなければ「なし」が表示されるというわけですね。

 

今日はここまで。またSQLについて学んだことをまとめて公開していこうと思っています。