Oracleの文字列関数(連結、置換、切り取り)の使い方をまとめていきます。
- 文字列同士を連結する
- 文字列を置換する（NULL値を別の値に置き換える）
- 文字列を置換する（別の文字に置き換える）
- 文字列を置換する（大文字、小文字を置き換える）
- 文字列を置換する（全角、半角を置き換える）
- 文字列を置換する（スペースを取り除く）
- 文字列を置換する（先頭、後方を埋める）
- 文字列の指定箇所を切り取る
広告
文字列同士を連結する
文字列同士を連結する場合、CONCAT関数を使用します。
| 文法 | CONCAT(str1, str2) |
| 引数 | str1：文字列 str2：str1に連結する文字列 |
| 戻り値 | 文字列str1と文字列str2を連結した値を返却します。 ※3つ以上を連結する場合はCONCAT関数の中にCOCAT関数を埋め込み使用します。 ※||を使用しても文字列を連結できます。 |
使用例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SQL>-- 生徒の名前に「さん」を連結する SQL>SELECT CONCAT(NAME, 'さん') as 名前 FROM T_STUDENT; 名前 --------------- Suzukiさん Yamashitaさん Yamadaさん Tanakaさん Aoyamaさん SQL> SQL>-- 3つ以上の文字列を連結する SQL>SELECT CONCAT(CONCAT(CLASS, '組の'), NAME) as 名前 FROM T_STUDENT; 名前 --------------- 1組のSuzuki 1組のYamashita 2組のYamada 2組のTanaka 2組のAoyama SQL> SQL>-- 3つ以上の文字列を連結する SQL>SELECT CLASS || '組の' || NAME || 'さん' as 名前 FROM T_STUDENT; 名前 --------------- 1組のSuzukiさん 1組のYamashitaさん 2組のYamadaさん 2組のTanakaさん 2組のAoyamaさん |
| Class | NAME |
| 1 | Suzuki |
| 1 | Yamashita |
| 2 | Yamada |
| 2 | Tanaka |
| 2 | Aoyama |
文字列を置換する（NULL値を別の値に置き換える）
NULLを別の値に置き換える場合、NVL関数、NVL2関数を使用します。
| 文法 | NVL(column, str) |
| 引数 | column：項目 str：置換文字列 |
| 戻り値 | columnがNULL値以外の場合はcolumn、NULL値の場合、strに指定した文字列を返却します。 |
| 文法 | NVL2(column, str1, str2) |
| 引数 | column：項目 str1：columnがNULL値以外の場合の置換文字列 str2：columnがNULL値の場合の置換文字列 |
| 戻り値 | columnがNULL値以外の場合はstr1、NULL値の場合にstr2に指定した文字列を返却します。 |
使用例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SQL>-- クラスと名前を抽出 SQL>SELECT NVL(Class, '不明') as Clas, Name FROM T_STUDENT; CLASS 名前 -------- --------------- 1 Suzuki 1 Yamashita 2 Yamada 2 Tanaka 不明 Aoyama SQL> SQL>-- クラスと名前を抽出 SQL>SELECT NVL2(Class, CONCAT(Class, '組'), '不明') as Clas, Name FROM T_STUDENT; CLASS 名前 -------- --------------- 1組 Suzuki 1組 Yamashita 2組 Yamada 2組 Tanaka 不明 Aoyama |
| CLASS | NAME |
| 1 | Suzuki |
| 1 | Yamashita |
| 2 | Yamada |
| 2 | Tanaka |
| NULL | Aoyama |
文字列を置換する（別の文字に置き換える）
文字列を別の文字列に置き換える場合、REPLACE関数、TRANSLATE関数を使用します。
| 文法 | REPLACE(str, target, replace) |
| 引数 | str：置換対象の文字列 target：置換前の文字列 replace：置換後の文字列 |
| 戻り値 | 置換対象文字列(str)から置換前文字列(target)を検索し、置換後文字列(replace)に置き換えた文字列を返却します。 |
| 文法 | TRANSLATE(str, targetlist, replacelist) |
| 引数 | str：置換対象の文字列 targetlist：置換前の文字リスト replacelist：置換後の文字リスト |
| 戻り値 | 置換対象文字列(str)から置換前文字リスト(targetlist)にある文字を検索し、該当した文字列位置にある置換後文字リスト(replacelist)に置き換えた文字列を返却します。 |
使用例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL>-- 文字列置換(REPLACE) SQL>SELECT REPLACE('abcdefgab', 'ab', '12') as 置換 FROM DUAL; 置換 --------------- 12cdefg12 SQL> SQL>-- 文字列置換(TRANSLATE) SQL>SELECT TRANSLATE('3647', '0123456789', '零一二三四五六七八九') as 置換 FROM DUAL; 置換 --------------- 三六四七 |
文字列を置換する（大文字、小文字を置き換える）
文字列の大文字、小文字を置き換える場合、UPPER、LOWER、INITCAP関数を使用します。
| 文法 | UPPER(str) |
| 引数 | str：文字列 |
| 戻り値 | 指定した文字列を大文字にして返却します。 |
| 文法 | LOWER(str) |
| 引数 | str：文字列 |
| 戻り値 | 指定した文字列を小文字にして返却します。 |
| 文法 | INITCAP(str) |
| 引数 | str：文字列 |
| 戻り値 | 指定した文字列の先頭を大文字、以降を小文字にして返却します。 |
使用例
|
1 2 3 4 5 6 7 8 9 10 |
SQL>-- 生徒の名前 SQL>SELECT UPPER(NAME) as U, LOWER(NAME) as LNAME, INITCAP(NAME) as INAME FROM T_STUDENT; UNAME LNAME INAME --------------- --------------- --------------- SUZUKI suzuki Suzuki YAMASHITA yamashita Yamashita YAMADA yamada Yamada TANAKA tanaka Tanaka AOYAMA aoyama Aoyama |
| CLASS | NAME |
| 1 | SUZUKI |
| 1 | Yamashita |
| 2 | yamada |
| 2 | tanaka |
| 2 | Aoyama |
文字列を置換する（全角、半角を置き換える）
文字列の全角文字、半角文字を置き換える場合、TO_MULTI_BYTE関数、TO_SINGLE_BYTE関数を使用します。
| 文法 | TO_MULTI_BYTE(str) |
| 引数 | str：半角文字列 |
| 戻り値 | 指定した文字列を全角文字に変換した文字列を返却します。 ※全角文字、半角カタカナは変換されません。 |
| 文法 | TO_SINGLE_BYTE(str) |
| 引数 | str：全角文字列 |
| 戻り値 | 指定した文字列を半角文字に変換した文字列を返却します。 ※半角文字、全角カタカナは変換されません。 |
使用例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL>-- 全角変換 SQL>SELECT TO_MULTI_BYTE('Japan 123 ｼﾞｬﾊﾟﾝ +-= あいうえお') as 変換 FROM DUAL; 変換 --------------- Ｊａｐａｎ １２３ ｼﾞｬﾊﾟﾝ ＋－＝ あいうえお SQL> SQL>-- 半角変換 SQL>SELECT TO_SINGLE_BYTE('Ｊａｐａｎ １２３ ジャパン ＋－＝ ABC') as 変換 FROM DUAL; 変換 --------------- Japan 123 ジャパン +-= ABC |
広告
文字列を置換する（スペースを取り除く）
文字列のスペースを取り除く場合は、TRIM関数、RTRIM関数、LTRIM関数を使用します。
| 文法 | TRIM(str [,delstr]) |
| 引数 | str：対象の文字列 delstr：削除したい文字列 ※省略した場合は半角スペースが対象 |
| 戻り値 | 対象文字列(str)の先頭、末尾から削除したい文字列(delstr)を削除した文字列を返却します。 |
| 文法 | RTRIM(str [,delstr]) |
| 引数 | str：対象の文字列 delstr：削除したい文字列 ※省略した場合は半角スペースが対象 |
| 戻り値 | 対象文字列(str)の末尾から削除したい文字列(delstr)を削除した文字列を返却します。 |
| 文法 | LTRIM(str [,delstr]) |
| 引数 | str：対象の文字列 delstr：削除したい文字列 ※省略した場合は半角スペースが対象 |
| 戻り値 | 対象文字列(str)の先頭から削除したい文字列(delstr)を削除した文字列を返却します。 |
使用例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SQL>-- 前後半角スペース削除 SQL>SELECT TRIM(' あいう えお ') as 削除 FROM DUAL; 削除 --------------- あいう えお SQL> SQL>-- 末尾0削除 SQL>SELECT RTRIM('1234500100', '0') as 削除 FROM DUAL; 削除 --------------- 12345001 SQL> SQL>-- 先頭削除 SQL>SELECT LTRIM('00101234500100', '0') as 削除 FROM DUAL; 削除 --------------- 101234500100 |
文字列を置換する（先頭、後方を埋める）
文字列の先頭、後方を指定文字で埋め、指定の桁数にする場合はLPAD関数、RPAD関数を使用します。
| 文法 | LPAD(str, len [, addstr]) |
| 引数 | str：文字列 len：全体の文字列長 addstr：追加する文字列 ※省略した場合は半角スペースが対象 |
| 戻り値 | 文字列(str)の先頭に追加する文字列(addstr)を連結し、全体文字列長(len)に加工した値を返却します。 ※文字列(str)が全体文字列長(len)より長い場合、末尾文字列が切り取られます。 |
| 文法 | RPAD(str, len [, addstr]) |
| 引数 | str：文字列 len：全体の文字列長 addstr：追加する文字列 ※省略した場合は半角スペースが対象 |
| 戻り値 | 文字列(str)の末尾に追加する文字列(addstr)を連結し、全体文字列長(len)に加工した値を返却します。 ※文字列(str)が全体文字列長(len)より長い場合、末尾文字列が切り取られます。 |
使用例
|
1 2 3 4 5 6 7 8 9 10 |
SQL>-- Noの先頭を0で埋める、名前末尾を#で埋める SQL>SELECT LPAD(NO, 7, '0') as 番号, RPAD(NAME, 10, '#') as 名前 FROM T_STUDENT; 番号 名前 ------- ----------- 000A101 Suzuki#### 000A102 Yamashita# 000B101 Yamada#### 000B102 Tanaka#### 000B103 Aoyama#### |
| No | NAME |
| A101 | Suzuki |
| A102 | Yamashita |
| B101 | Yamada |
| B102 | Tanaka |
| B103 | Aoyama |
文字列の指定箇所を切り取る
文字列を指定の箇所で切り取る場合、SUBSTR関数、SUBSTRB関数を使用します。
| 文法 | SUBSTR(str, position [,len]) |
| 引数 | str：文字列 position： 正＝切り出したい文字の開始位置(1から) 負＝末尾から逆方向にカウントした文字位置 len：取り出す文字長 ※省略した場合は末尾までが対象 |
| 戻り値 | 文字列(str)の開始文字位置(position)から文字長(len)までの文字を切り出し返却する。 |
| 文法 | SUBSTRB(str, position [,len]) |
| 引数 | str：文字列 position： 正＝切り出したいバイトの開始位置(1から) 負＝末尾から逆方向にカウントしたバイト位置 len：取り出すバイト長 ※省略した場合は末尾までが対象 |
| 戻り値 | 文字列(str)の開始バイト位置(position)からバイト長(len)までの文字を切り出し返却する。 |
使用例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL>-- 文字列を切り出す SQL>SELECT SUBSTR('アイウエオ', 4) as 切り出し FROM DUAL; 切り出し --------------- エオ SQL> SQL>-- 文字列を切り出す SQL>SELECT SUBSTRB('アイウエオ', 4) as 切り出し FROM DUAL; 切り出し --------------- ウエ |
