SAK 図書館

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

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 編資料
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索



Copyright (C) 1999-2017 Y.SAK