SAK 図書館
SAK 図書館
SQL 文字関数 - 連結、空白削除、置換、切出、検索、長さ、数値変換他
基本的に Oracle、SQL-Server、MDB で共通です。
■文字列連結
・concat() 関数は、文字列を結合するした結果を返す。
「||」演算子の方が簡単と思うがどうでしょう。
(Access では 「&」を使用する。)
select 品番, concat('part-', 品番), 'part-' || 品番 from sak.受注m
order by 品番
;
■chr 文字変換
・chr() 関数は、コードに該当する ASCII 文字を返す。
この例では、「1」と言う文字が返る。
select 品番, chr(49) from sak.受注m
order by 品番
;
■ascii コード変換
・ascii() 関数は、文字に該当する ASCII コードを返す。
この例では、「49」と言う数字が返る。
select 品番, ascii('1') from sak.受注m
order by 品番
;
■小文字化
・lower() 関数は、英字を小文字にして返す。
この例では、品番に大文字があると全て小文字で返る。
select lower(品番), 受注数 from sak.受注m
order by 品番
;
■大文字化
・upper() 関数は、英字を大文字にして返す。
この例では、品番に小文字があると全て大文字で返る。
select upper(品番), 受注数 from sak.受注m
order by 品番
;
■左文字埋め
・lpad() 関数は、文字列の左に指定文字を埋めて指定数切り出します。
(切り出し指定数になるまで繰り返し埋めます。)
select lpad('abc', 5, '0') from dual;
LPAD(
-----
00abc
select lpad('abc', 5, '012') from dual;
LPAD(
-----
01abc
・指定文字省略値は ' ' 半角スペースです。
select lpad('abc', 5) from dual;
LPAD(
-----
abc
select lpad('abc', 5, ' ') from dual;
LPAD(
-----
abc
・次の例では、品番の左に「0」を埋めた、右から 8 文字固定の文字列となり
ます。
select lpad(品番, 8, '0'), 受注数 from sak.受注m
order by 品番
;
LPAD(品番,8,'0') 受注数
---------------- ----------
0000a001 500
0000b001 2000
■右文字埋め
・rpad() 関数は、文字列の右に指定文字を埋めて指定数切り出します。
(切り出し指定数になるまで繰り返し埋めます。)
select rpad('abc', 5, '0') from dual;
RPAD(
-----
abc00
select rpad('abc', 5, '012') from dual;
RPAD(
-----
abc01
・指定文字省略値は ' ' 半角スペースです。
select rpad('abc', 5) from dual;
RPAD(
-----
abc
select rpad('abc', 5, ' ') from dual;
RPAD(
-----
abc
・次の例では、品番の右に「_」を埋めた、左から 8 文字固定の文字列となり
ます。
select rpad(品番, 8, '_'), 受注数 from sak.受注m
order by 品番
;
RPAD(品番,8,'_') 受注数
---------------- ----------
a001____ 500
b001____ 2000
■左空白削除
・ltrim() 関数は、文字列の左側の空白を除去します。
この例では、品番の左側に空白があれば、除去されます。
select ltrim(品番), 受注数 from sak.受注m
order by 品番
;
■右空白削除
・rtrim() 関数は、文字列の右側の空白を除去します。
この例では、品番の右側に空白があれば、除去されます。
select rtrim(品番), 受注数 from sak.受注m
order by 品番
;
■左右空白削除
・ltrim()、rtrim() 関数を組み合わせて実現します。
この例では、品番の左右の空白が除去されます。
select ltrim(rtrim(品番)), 受注数 from sak.受注m
order by 品番
;
■文字列置き換え
・replace() 関数は、文字列の置き換えをする。
この例では、品番に「a0」と言う文字列があれば、「b1」に置き換えられる。
select replace(品番, 'a0', 'b1'), 受注数 from sak.受注m
order by 品番
;
・「a0a1a2」中の a0 を b1 に置き換える場合、次のようにします。
select replace('a0a1a2', 'a0', 'b1') from dual;
REPLAC
------
b1a1a2
■文字列置き換え
・translate() 関数は、凝った文字列の置き換えをする。
ソース文字列に対して対象文字「0123456789abc」に該当する部分を
「0123456789abc」に対になった「0000000000AAA」で置き換えます。
つまり、「0123456789abc」は、0〜9、a〜c なので、ソース文字全部に該当
します。そして、0 → 0、1 → 0、...、a → A、b→ A、... の置き換え、
0〜9 → 0、a〜c → A で「AAA000」になります。
select translate('abc012', '0123456789abc', '0000000000AAA') from dual;
|_ ソース |_ 対象文字 |_ 対象文字に対する置き換え文字
TRANSL
------
AAA000
・カットもできる。この例では、「000」が返る。
select translate('abc012', '0123456789abc', '0000000000') from dual;
TRA
---
000
■文字列切り出し
・substr() 関数は、文字列の切り出しをする。
(Access では mid を使用する。)
この例では、品番の 2 文字目から 3 文字分を切り出す。
日本語対応関数なので、バイト単位の処理をしたい場合は、substrb() を
使用する。
select substr(品番, 2, 3), 受注数 from sak.受注m
order by 品番
;
・開始位置以降、最後まで切り出す場合は、切り出す文字数を省略できる。
select substr(品番, 2), 受注数 from sak.受注m
order by 品番
;
■文字列検索
・instr() 関数は、文字列の検索を行う。
この例では、品番の 2 文字目以降から「0」を検索して、その位置から
substr() で切り出した文字列を返します。
日本語対応関数なので、バイト単位の処理をしたい場合は、instrb() を
使用する。
select substr(品番, instr(品番, '0', 2, 1)), 受注数 from sak.受注m
order by 品番
;
・開始位置が負の場合は、後ろから検索する。
この例では、品番の後ろ側から「0」を検索して、その位置から
substr() で切り出した文字列を返します。
select substr(品番, instr(品番, '0', -1, 1)), 受注数 from sak.受注m
order by 品番
;
■文字列の長さ
・length() 関数は、文字列の長さを返す。
この例では、品番の文字数を返します。
この関数を単独で使うことにあまり意味はないと思います。
substr()、instr() との組み合わせで使うことが大半です。
日本語対応関数なので、バイト単位の処理をしたい場合は、lengthb() を
使用する。
select length(品番), 受注数 from sak.受注m
order by 品番
;
・この例では、品番の後ろから 3 文字を切り出す。
select substr(品番, length(品番) - 2), 受注数 from sak.受注m
order by 品番
;
■数値から文字列へ変換 (数値フォーマット、書式変換)
・to_char() 関数は、数値を文字列型に変換します。
この例では、受注数を文字列として返します。
select 品番, to_char(受注数) from sak.受注m
order by 品番
;
・三桁毎に数値にカンマ「,」を入れたい場合、次のようにします。
select 品番, to_char(受注数, '999,999,999,999') from sak.受注m
order by 品番
;
・小数点以下をフォーマットする場合は、次のようにします。
select 品番, to_char(受注数, '999,999,999,999.99') from sak.受注m
order by 品番
;
・ゼロ埋め(0 埋め) フォーマットする場合は、次のようにします。
select 品番, to_char(受注数, '000000000') from sak.受注m
order by 品番
;
■文字列から数値へ変換
・to_number() 関数は、文字列を数値に変換します。
この例では、受注数を文字列としたものを、また数値に変換しています。
現実的な例ではありませんが機能はわかるはずです。
select 品番, to_number(to_char(受注数)) from sak.受注m
order by 品番
;
■型変換
・cast() 関数は、ANSI 規程の変換関数です。
SQL-Server の convert() 関数は、Oracle では、to_char()、to_number()、
to_date() に相当します。
select 品番, cast(受注数 as varchar2(20)) from sak.受注m
order by 品番
;
select cast('99-01-01' as date) from dual;
■SQL 基礎実地編資料
■SQL 基礎編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
Copyright (C) 1999-2018 Y.SAK