[SQL] 文字列を操作する

SQL 文字列操作

文字列は何かと使いやすいように加工することがある。その方法について例を挙げながら説明していく。

文字列の一部を取り出す(SUBSTR)

SUBSTRはSUBSTRINGの略。文字列から一部分を取り出す。

使い方は2つの方法がある。まずは文字列のカラムと何文字目から取り始めるかを指定する方法。

SELECT
SUBSTR(文字列のカラム,何文字目からか)
FROM テーブル

もう1つの方法は、さらに何文字取るかまで指定する方法。

SELECT
SUBSTR(文字列のカラム,何文字目からか,何文字分取るか)
FROM テーブル

書き方 結果 説明
SUBSTR('abcde' , 3) cde 3文字目から最後まで
SUBSTR('あいうえお' , 3) うえお 全角文字も1文字として扱われれる
SUBSTR('abcde' , 3 ,2) cd 3番目から2文字分
SUBSTR('あいうえお' , 1 , 4) あいうえ 最初(1文字目)から4文字

なお文字列の一部なので全て文字列。'12345'という文字列の部分を取って'34'にしても数値にはならない。

住所から都道府県を取り出したり文字列操作の中でも多用する。

文字列を結合する(CONCAT)

CONCATで文字列を結合する。

CONCAT(文字列1,文字列2,・・・)

CONCAT(年代,'_',性別)のように間に文字を入れて新しい区分を作ったりする。

NULLがある場合

NULLがあるとCONCATはエラーになるのでIFNULLなどで何かの文字列または空欄に変換する必要がある。例えば文字列2にNULLがある場合は、

CONCAT(文字列1,IFNULL(文字列2,'xxx'),・・・)

とxxxという文字列(性別なら不明などがいいだろう)に変換する。あるいは

CONCAT(文字列1,IFNULL(文字列2,''),・・・)

とNULLを空欄に変換にすることもできる。

数値を結合したい場合

数値はそのままでは結合できないので文字列に変換が必要。

CONCAT(文字列1,CAST(数値型 as STRING),・・・)

型を変換するCASTについては別に触れる。

文字列を結合する(||)

以前はBigQueryでは||での文字列結合はできなかったのだがいつの間にかできるようになっていた。

文字列1||文字列2||・・・

数値型でもCASTでの型変換が不要で自動的に結合してくれるので便利。

不要な文字を取り除く(TRIM)

文字列の前後にある不要な文字(よくあるのはスペース)を削除する

TRIM(文字列,消したい文字)

消したい文字が指定されていない場合はスペース(半角だけでなく全角も)になる。

TRIM (' 田中 一郎   ')

は'田中 一郎'になる。前後は消えるが中間は消えない。

前だけ消したいならLTRIM、後ろだけ消したいならRTRIMがある。書き方はTRIMと同様。

文字を埋める(LPAD,RPAD)

LPAD(文字列,文字数)

LPAD(文字列,文字数,埋める文字)

文字数が文字列より大きい場合は、足りない分を左から指定した文字で埋める。何も指定が無ければ半角スペースになる。

もし文字数が文字列より小さい場合は、その文字数より先は切り捨てる。

書き方 結果 説明
LPAD('12345',7) ' 12345' 最初の2文字は半角スペース
LPAD('12345',3) '123' 最初の3文字
LPAD('12345',7,'x') 'xx12345' 足りない分を左から'x'で埋める
LPAD('1',5,'0') '00001' 足りない分を左から'0'で埋める

最後の方法はcsvをExcelで開いてしまってIDの先頭の0が消えてしまった際に0で埋めなおす場合にも使える。

RPAD(RIGHT PADDNG)は右から埋める。書き方はLPADと同じ。

文字列を置換する(REPLACE)

ある文字列を別の文字列に置換する。

REPLACE(元の文字列 , 置換する前の文字 , 置換した後の文字)

書き方 結果 説明
REPLACE('123', '23', '34') '134' 23が24に置換された
REPLACE('123', '2', '') '13' 2が空欄に置換された(2が消えた)
REPLACE('212322', '2', '') '13' いくつあっても消える

空文字に置換することでよけいなスペースを消すといった使い方もする。入れ子にすることで複数の文字を同時に消すことも可能。

より複雑な置換には正規表現を使うREGEXP_REPLACEがある。

文字列を分割する(SPLIT)

文字列をカンマやスペースで区切って分割したい時に使う。結果は配列になる。アクセスするにはカラム名[OFFSET(インデックス)]

SPLIT(文字列,区切り文字)

区切り文字が無い場合はカンマになる

SPLIT('田中 一郎' , ' ')

この結果は配列の第1要素に「田中」、第2要素に「一郎」が入る。

SQL

Posted by 管理人