SQLの文字関数で文字データを自在に操作する

SQLには、文字列を操作する関数が用意されています。文字列同士の結合や特定文字数の取得など、文字列を対象に操作します。

覚えるべきキーワード
・CONCAT
・SUBSTR
・INSTR
・LENGTH
・LPAD/RPAD
・REPLACE
・TRIM

CONCAT

CONCATは、2つの文字列を結合して表示するSQL関数です。

CONCAT

SELECT CONCAT(‘Engi’,’neer’) “JOB” FROM dual;

このSQLは、Engiとneerの2つの文字列を結合して1つの文字列にします。左側の文字列(第1引数)が先で、右側の文字列(第2引数)を後にして結合します。2つの文字列しか結合できませんので、3つ以上はエラーになります。ただし方法はあります。それはCONCAT関数をネストする方法です。

CONCATのネスト

SELECT CONCAT(CONCAT’(‘Sys’,’tem’),’Engineer’) “JOB” FROM dual;

これは3つ以上の文字列を結合するSQLです。CONCATをネストしています。まず、Sysとtemを結合します。そして、その結合結果(System)とEngineerを結合します。4つ以上を結合する場合はさらにネストすれば結合可能です。dual表を使わずにテーブルの列名を使うときは以下のように書きます。

CONCAT

SELECT CONCAT(first_name,second_name) FROM employee;

このSQLはemployeeテーブルにあるfirst_name列とsecond_name列を結合しています。

SUBSTR

SUBSTRは、指定した文字列に対して、指定した文字位置から指定した文字数分を戻すSQL関数です。以下のSQLが例です。

SUBSTR

SELECT SUBSTR(‘ENGINEER’ ,3,4) “JOB” FROM dual;

Engineerという文字列に対して、3文字目から4文字分を戻します。ENGINEERの3文字目はGです。そして、Gから4文字分はGINEなので、このSQLを実行するとGINEを戻します。ちなみに文字数を未指定の場合は指定した文字位置からすべてを返します。

SUBSTR

SELECT SUBSTR(‘ENGINEER’,3) “JOB” FROM dual;

このSQLは文字数を指定していません。そのため3文字目からすべての文字列を返すので、GINEERを返します。

文字位置を最後から指定したい場合は指定位置に-(マイナス)をつけます。

SUBSTRの指定位置を-(マイナス)

SELECT SUBSTR(‘ENGINEER’,-4,3) “JOB” FROM dual;

-4を指定しているので、最後から数えると、REENでnが-4番目です。nから3文字分なので、NEEを返します。

INSTR

INSTRは文字列から指定した文字を検索し、その文字位置を数字で返す関数です。指定した文字が最初から数えて何番目にあるかを返します。また同じ文字が複数ある場合に備えて、何回目にあるかも指定できます。

INSTR

SELECT INSTR(’ ENGINEER’,’N’,1,2) FROM dual;

このSQLは、Nを1番目から数えて2回目にある文字位置を返します。2回目のNはIとEの間のNなので、最初から数えると5番目です。そのためこのSQLを実行すると5が帰ります。ここでは1を指定したので、Eから検索しますが、4と指定したらIから検索を開始して、2回目にNが見つかった位置を返します。
検索開始位置や何回目にあるかを指定せずに実行することも可能です。

INSTR

SELECT INSTR(’ ENGINEER’,’N’) FROM dual;

検索開始位置や何回目かを未指定だと、1番目から検索を開始して最初に検索文字を見つけた文字位置を返します。ちなみに指定文字がない場合は0が返ります。

INSTR

SELECT INSTR(’ ENGINEER’,’P’) FROM dual;

ENGINEERにはPがないですね。なので、返るのは0です。

LENGTH

LENGTHは文字列の長さを返すSQL関数です。指定できる文字列は1つのみです。
日本語でもアルファベットでも1文字は1文字として返します。空白は1つの空白につき、1文字となります。

LENGTH

SELECT LENGTH(‘SYSTEM ENGINEER’) FROM dual;

そのため、このSYSTEM ENGINEERは15文字です。アルファベット14文字と空白が1文字なので、15文字です。もちろん、SYSTEMとENGINEERの間の空白を2文字にすれば文字数は16文字になります。
次は日本語文字のLENGTHです。

LENGTH

SELECT LENGTH(‘システムエンジニア) FROM dual;

日本語はマルチバイトですが、文字数には関係ないため、この実行結果は9文字です。

LPAD/RPAD

LPADとRPADは指定した文字数分に達するように指定した文字を埋め込みます。LPADは指定した文字を左側に埋め込み、RPADは右側に埋め込みます。LEFTのLPADとRIGHTのRPADですね。

LPAD

SELECT LPAD(‘ENGINEER’,12,’/‘) “JOB” FROM dual;

12文字になるように指定した文字を埋め込みます。ENGINEERが8文字なので、/を左側に4文字分埋め込むため、結果は//// ENGINEERになります。
RPADは右側に埋め込みます。

RPAD

SELECT RPAD(‘ENGINEER’,12,’/‘) “JOB” FROM dual;

このSQLの実行結果はENGINEER////です。

REPLACE

文字列を置換する関数がREPLACEです。対象の文字列の中から置換したい文字列と置換後の文字列を指定します。

REPLACE

SELECT REPLACE(‘ENGINEER’,’NEER’,’NE’) “ JOB” FROM dual;

ENGINEERの中のNEERをNEに変換するREPLACEです。実行結果はENGINEになります。

TRIM

TRIMは文字列から指定した文字列を除去します。指定できる文字列は1文字のみで、除去対象は先頭と最後の両端の文字になります。

TRIM

SELECT TRIM(‘/’ FROM ‘///SYSTEM//ENGINEER//’) “JOB” FROM dual;

他の文字関数とは違っていて、少し書き方が特徴的です。これを実行すると先頭の///と最後の//が取り除かれます。SYSTEMとENGINEERの間にある//は取り除かれません。あくまで両端の文字が対象です。実行結果はSYSTEM//ENGINEERとなります。

あと、両端の空白を除去したいときは以下のように書きます。

TRIMの空白除去

SELECT TRIM(‘ SYSTEM ENGINEER ‘) “JOB” FROM dual;

これで先頭と最後の空白が取り除かれます。実際、システム開発の現場では、この空白除去のTRIMをよく使います。

経験上、文字関数はいろんなソースでよく見ましたので、知っておかないとマズいです。特にSUBSTRとTRIMは使用頻度が高いです。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です