-
×
[PR]上記の広告は3ヶ月以上新規記事投稿のないブログに表示されています。新しい記事を書く事で広告が消えます。
-
MySQL ではいろんな数学関数があります。三角関数（SIN, COSIN, TAN, LOG, ...）や対数に関する（LOG, ...）等がありますが、 これらは通常であればほぼ使う機会が無いと思います。 私自身も三角関数や対数は使ったことがありません。（科学計算を行うシステムであれば必要かもしれませんが）
数学関数のなかで使うとすれば、小数点以下の丸めなどを行う CEILING、FLOOR、ROUND、TRUNCATE 関数だと思います。■CEILING（最小整数値）
CEILING 関数は、与えられた数値以上で最小の整数値を返します。
CEILING 関数は以下の様な引数をとります。CEILING(X) -- X : 数値データ（小数点以下ありの正確値、浮動小数点型データなど） -- CEIL(X) はシノニム
それでは、CEILING 関数の使用例を示します。MariaDB [(none)]> SELECT -> CEILING(1.25) AS '小数以下ありの正の値' -> ,CEILING(-1.25) AS '小数以下ありの負の値' -> ,CEILING('1.25') AS '文字列の小数以下ありの正の値' -> ,CEILING('-1.25') AS '文字列の小数以下ありの負の値' -> ; +----------------------+----------------------+------------------------------+------------------------------+ | 小数以下ありの正の値 | 小数以下ありの負の値 | 文字列の小数以下ありの正の値 | 文字列の小数以下ありの負の値 | +----------------------+----------------------+------------------------------+------------------------------+ | 2 | -1 | 2 | -1 | +----------------------+----------------------+------------------------------+------------------------------+ 1 row in set (0.00 sec)確かに与えられた数値以上で最小の整数値を返してのが分かります。
また、上の例の様に与えるデータは文字列でも同様の結果が得られます。
■FLOOR（最大整数値）
FLOOR 関数は、与えられた数値以下で最大の整数値を返します。
FLOOR 関数は以下の様な引数をとります。FLOOR(X) -- X : 数値データ（小数点以下ありの正確値、浮動小数点型データなど）
それでは、FLOOR 関数の使用例を示します。MariaDB [(none)]> SELECT -> FLOOR(1.25) AS '小数以下ありの正の値' -> ,FLOOR(-1.25) AS '小数以下ありの負の値' -> ,FLOOR('1.25') AS '文字列の小数以下ありの正の値' -> ,FLOOR('-1.25') AS '文字列の小数以下ありの負の値' -> ; +----------------------+----------------------+------------------------------+------------------------------+ | 小数以下ありの正の値 | 小数以下ありの負の値 | 文字列の小数以下ありの正の値 | 文字列の小数以下ありの負の値 | +----------------------+----------------------+------------------------------+------------------------------+ | 1 | -2 | 1 | -2 | +----------------------+----------------------+------------------------------+------------------------------+ 1 row in set (0.00 sec)確かに与えられた数値以下で最大の整数値を返してのが分かります。 （「-1.25」の場合は「-1」の1個更にマイナスで一番近い値は「-2」なので結果「-2」となります。）
また、上の例の様に与えるデータは文字列でも同様の結果が得られます。
■ROUND（丸め処理）
ROUND 関数は、与えられた数値の指定小数点位置で丸め処理を行い値を返します。
ROUND 関数は以下の様な引数をとります。ROUND(X, DP) ROUND(X) -- X : 数値データ（小数点以下ありの正確値、浮動小数点型データなど） -- DP: 小数点位置（ROUND(X) は ROUND(X, 0) と同様） （DP がマイナスの場合は小数点から左側の位の大きい方を「0」にする）
それでは、ROUND 関数の使用例を示します。MariaDB [(none)]> SELECT -> ROUND(-1.25) -> ,ROUND(-1.51) -> ,ROUND(1.25) -> ,ROUND(1.51) -> ,ROUND(-1.251, 2) -> ,ROUND(-1.515, 2) -> ,ROUND(1.251, 2) -> ,ROUND(1.515, 2) -> ; +--------------+--------------+-------------+-------------+------------------+------------------+-----------------+-----------------+ | ROUND(-1.25) | ROUND(-1.51) | ROUND(1.25) | ROUND(1.51) | ROUND(-1.251, 2) | ROUND(-1.515, 2) | ROUND(1.251, 2) | ROUND(1.515, 2) | +--------------+--------------+-------------+-------------+------------------+------------------+-----------------+-----------------+ | -1 | -2 | 1 | 2 | -1.25 | -1.52 | 1.25 | 1.52 | +--------------+--------------+-------------+-------------+------------------+------------------+-----------------+-----------------+ 1 row in set (0.00 sec)確かに与えられた数値を指定小数点位置で丸め処理がされた値を返してのが分かります。 （数値がマイナスの場合は絶対値で丸め処理を行い、その値にマイナスをしている様な感じです。）
また、上の例の様に与えるデータは文字列でも同様の結果が得られます。MariaDB [(none)]> SELECT -> ROUND('-1.25') -> ,ROUND('-1.51') -> ,ROUND('1.25') -> ,ROUND('1.51') -> ,ROUND('-1.251', 2) -> ,ROUND('-1.515', 2) -> ,ROUND('1.251', 2) -> ,ROUND('1.515', 2) -> ; +----------------+----------------+---------------+---------------+--------------------+--------------------+-------------------+-------------------+ | ROUND('-1.25') | ROUND('-1.51') | ROUND('1.25') | ROUND('1.51') | ROUND('-1.251', 2) | ROUND('-1.515', 2) | ROUND('1.251', 2) | ROUND('1.515', 2) | +----------------+----------------+---------------+---------------+--------------------+--------------------+-------------------+-------------------+ | -1 | -2 | 1 | 2 | -1.25 | -1.52 | 1.25 | 1.52 | +----------------+----------------+---------------+---------------+--------------------+--------------------+-------------------+-------------------+ 1 row in set (0.00 sec)小数点位置がマイナスの場合の例を示します。
MariaDB [(none)]> SELECT -> ROUND(1251.23, -2) -> ,ROUND(1515.45, -2) -> ; +--------------------+--------------------+ | ROUND(1251.23, -2) | ROUND(1515.45, -2) | +--------------------+--------------------+ | 1300 | 1500 | +--------------------+--------------------+ 1 row in set (0.00 sec)■TRUNCATE（切り捨て）
TRUNCATE 関数は、与えられた数値の指定小数点位置以下の切り捨て処理を行い値を返します。
TRUNCATE 関数は以下の様な引数をとります。TRUNCATE(X, DP) -- X : 数値データ（小数点以下ありの正確値、浮動小数点型データなど） -- DP: 小数点位置（DP がマイナスの場合は小数点から左側の位の大きい方を「0」にする）
それでは、TRUNCATE 関数の使用例を示します。MariaDB [(none)]> SELECT -> TRUNCATE(1.234, 1) -> ,TRUNCATE(1.987, 1) -> ,TRUNCATE(1.999, 0) -> ,TRUNCATE(-1.999, 1) -> ,TRUNCATE(1234, -2) -> ; +--------------------+--------------------+--------------------+---------------------+--------------------+ | TRUNCATE(1.234, 1) | TRUNCATE(1.987, 1) | TRUNCATE(1.999, 0) | TRUNCATE(-1.999, 1) | TRUNCATE(1234, -2) | +--------------------+--------------------+--------------------+---------------------+--------------------+ | 1.2 | 1.9 | 1 | -1.9 | 1200 | +--------------------+--------------------+--------------------+---------------------+--------------------+ 1 row in set (0.00 sec)確かに与えられた数値を指定小数点位置以下で切り捨て処理がされた値を返してのが分かります。 （指定小数点位置がマイナスの場合は、小数点位置から上の位を「0」にしているのがわかります。）
また、上の例の様に与えるデータは文字列でも同様の結果が得られます。MariaDB [(none)]> SELECT -> TRUNCATE('1.234', 1) -> ,TRUNCATE('1.987', 1) -> ,TRUNCATE('1.999', 0) -> ,TRUNCATE('-1.999', 1) -> ,TRUNCATE('1234', -2) -> ; +----------------------+----------------------+----------------------+-----------------------+----------------------+ | TRUNCATE('1.234', 1) | TRUNCATE('1.987', 1) | TRUNCATE('1.999', 0) | TRUNCATE('-1.999', 1) | TRUNCATE('1234', -2) | +----------------------+----------------------+----------------------+-----------------------+----------------------+ | 1.2 | 1.9 | 1 | -1.9 | 1200 | +----------------------+----------------------+----------------------+-----------------------+----------------------+ 1 row in set (0.01 sec)
PR -
MySQL で文字列中の指定文字列の置き換えを行う方法として REPLACE 関数があります。
REPLACE 関数は以下の様な引数をとります。REPLACE(str, from_str, to_str) -- str : 対象文字列 -- from_str : 置換対象文字列 -- to_str : 置換文字列
なお、この関数はマルチバイトセーフです。
それでは、REPLACE 関数の使用例を示します。
■[REPLACE関数]の例
MariaDB [(none)]> SELECT -> REPLACE('0000-01-1234', '-', '/') AS '文字列の置換' -> ,REPLACE('0000-01-1234', '-', '**') AS '置換文字列が2文字以上' -> ,REPLACE('0000--01--1234', '--', '***') AS '置換対象文字列が2文字以上' -> ,REPLACE('0000--88--1234', '--', '') AS '置換文字列が空文字' -> ,REPLACE('0000--88--1234', '**', '') AS '置換対象文字列が存在無し' -> ; +--------------+-----------------------+---------------------------+--------------------+--------------------------+ | 文字列の置換 | 置換文字列が2文字以上 | 置換対象文字列が2文字以上 | 置換文字列が空文字 | 置換対象文字列が存在無し | +--------------+-----------------------+---------------------------+--------------------+--------------------------+ | 0000/01/1234 | 0000**01**1234 | 0000***01***1234 | 0000881234 | 0000--88--1234 | +--------------+-----------------------+---------------------------+--------------------+--------------------------+ 1 row in set (0.00 sec)確かに置換対象文字列、置換文字列が2文字以上であっても置換処理が行われるのが分かります。
また、置換文字列が空文字列の場合は、置換対象文字列を省いた文字列が返されます。（ある特定の文字を削除する時に使えそうです）
なお、置換対象文字列が、対象文字列に存在しない場合は置換処理無しで対象文字列そのものを返します。
■[REPLACE関数]のマルチバイトの例
それでは上記の例を全て全角の文字列に替えて実行してみます。
MariaDB [(none)]> SELECT -> REPLACE('００００－０１－１２３４', '－', '／') AS '文字列の置換' -> ,REPLACE('００００－０１－１２３４', '－', '＊＊') AS '置換文字列が2文字以上' -> ,REPLACE('００００－－０１－－１２３４', '－－', '＊＊＊') AS '置換対象文字列が2文字以上' -> ,REPLACE('００００－－88－－１２３４', '－－', '') AS '置換文字列が空文字' -> ,REPLACE('００００－－88－－１２３４', '＊＊', '') AS '置換文字列が存在無し' -> ; +--------------------------+------------------------------+----------------------------------+--------------------+----------------------------+ | 文字列の置換 | 置換文字列が2文字以上 | 置換対象文字列が2文字以上 | 置換文字列が空文字 | 置換文字列が存在無し | +--------------------------+------------------------------+----------------------------------+--------------------+----------------------------+ | ００００／０１／１２３４ | ００００＊＊０１＊＊１２３４ | ００００＊＊＊０１＊＊＊１２３４ | ００００88１２３４ | ００００－－88－－１２３４ | +--------------------------+------------------------------+----------------------------------+--------------------+----------------------------+ 1 row in set (0.00 sec)
-
MySQL で区切り文字列を使用した連結を行う方法として CONCAT_WS 関数があります。
以前説明しました以下の CONCAT_WS 関数の拡張版の様なものです。
⇒MySQL 文字列の連結する方法について（CONCAT、文字列を並べて配置）
なお CONCAT_WS 関数は以下の様な引数をとります。CONCAT_WS(separator, str1, str2, str3, ...) -- separator : 区切り文字列 -- str1, str2, str3, ... : 連結する文字列（NULL が在る場合はそれを無視する）
それでは、CONCAT_WS 関数の使用例を示します。
■[CONCAT_WS関数]の例
MariaDB [(none)]> SELECT -> CONCAT_WS('/', 'ABC', 'DEFG', 'あいう', 'HI') AS '文字列の連結' -> ,CONCAT_WS('/', 'ABC', 100, '全角') AS 'リテラル数値を連結' -> ,CONCAT_WS('/', 100, 200) AS 'リテラル数値のみを連結' -> ,CONCAT_WS('/', 'ABC', NULL, '全角') AS 'NULLを連結' -> ,CONCAT_WS('/', 'ABC', NULL) AS 'NULLを連結2' -> ; +--------------------+--------------------+------------------------+------------+-------------+ | 文字列の連結 | リテラル数値を連結 | リテラル数値のみを連結 | NULLを連結 | NULLを連結2 | +--------------------+--------------------+------------------------+------------+-------------+ | ABC/DEFG/あいう/HI | ABC/100/全角 | 100/200 | ABC/全角 | ABC | +--------------------+--------------------+------------------------+------------+-------------+ 1 row in set (0.00 sec)確かに数値の場合も文字列に変換されて処理されるのが分かります。
また、引数の中に NULL が存在する場合は NULL をスキップした文字列が返されます。
なお、最後が NULL で終わる場合は最後の連結文字列は追加されないので、5個目の例の様に連結文字列が無い場合もあります。
上記の例では、全てリテラルの文字列を引数に指定しましたが、 リテラルであればそれらを単に羅列して SQL に記述すれば連結する様です。
なお、連結文字列は文字列なので2文字以上でも可能です。
■[連結文字列が2文字以上]の例
MariaDB [(none)]> SELECT -> CONCAT_WS('//', 'ABC', 'DEFG', 'あいう', 'HI') AS '文字列の連結' -> ,CONCAT_WS('//', 'ABC', 100, '全角') AS 'リテラル数値を連結' -> ,CONCAT_WS('//', 100, 200) AS 'リテラル数値のみを連結' -> ,CONCAT_WS('//', 'ABC', NULL, '全角') AS 'NULLを連結' -> ,CONCAT_WS('//', 'ABC', NULL) AS 'NULLを連結2' -> ; +-----------------------+--------------------+------------------------+------------+-------------+ | 文字列の連結 | リテラル数値を連結 | リテラル数値のみを連結 | NULLを連結 | NULLを連結2 | +-----------------------+--------------------+------------------------+------------+-------------+ | ABC//DEFG//あいう//HI | ABC//100//全角 | 100//200 | ABC//全角 | ABC | +-----------------------+--------------------+------------------------+------------+-------------+ 1 row in set (0.00 sec)この例によれば、1個上の連結文字列が「//」に変更されていることがわかります。
■[CONCAT_WS関数]をテーブルデータを引数にする例
テーブルデータを引数に指定するために、データベースを指定し「t_auto」なるテストテーブルの一覧を取得します。
MariaDB [(none)]> USE test; Database changed MariaDB [test]> SET NAMES cp932; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> SELECT * FROM t_auto; +----+--------+ | id | val | +----+--------+ | 1 | test1 | | 2 | test2 | | 4 | test4 | | 5 | test-a | | 6 | あいう | +----+--------+ 5 rows in set (0.00 sec)
それではこのテーブルの「id」と「val」の連結を一覧してみます。
MariaDB [test]> SELECT CONCAT_WS('-', id, val) FROM t_auto; +-------------------------+ | CONCAT_WS('-', id, val) | +-------------------------+ | 1-test1 | | 2-test2 | | 4-test4 | | 5-test-a | | 6-あいう | +-------------------------+ 5 rows in set (0.03 sec)
-
表示したい文字列の前後に何かの文字列を追加したい場合はたまにあると思います。 例えば、空白を埋めたりする場合です。
MySQL では文字列の先頭から追加する方法として LPAD 関数、後ろから追加する関数として RPAD があります。
なお、これらの関数はマルチバイトセーフです。
LPAD、RPAD 関数の説明は以下の通りです。■[LPAD、RPAD関数]の説明
LPAD(str, len, padstr) -- str : 対象文字列（str が len よりも長い場合は、戻り値は len 文字に短縮されます。） -- len : 最終的な文字列の長さ -- padstr : 先頭から追加（パディング）する文字列 RPAD(str,len,padstr) -- str : 対象文字列（str が len よりも長い場合は、戻り値は len 文字に短縮されます。） -- len : 最終的な文字列の長さ -- padstr : 右に追加（パディング）する文字列
それでは順次、FORMAT 関数の使用例を示します。
■[LPAD、RPAD関数]の例
MariaDB [(none)]> SELECT -> LPAD('123', 5, '**') -> ,LPAD('123', 2, '**') -> ,RPAD('123', 5, '*') -> ,RPAD('123', 2, '*') -> ,RPAD('', 10, '*') -> ; +----------------------+----------------------+---------------------+---------------------+-------------------+ | LPAD('123', 5, '**') | LPAD('123', 2, '**') | RPAD('123', 5, '*') | RPAD('123', 2, '*') | RPAD('', 10, '*') | +----------------------+----------------------+---------------------+---------------------+-------------------+ | **123 | 12 | 123** | 12 | ********** | +----------------------+----------------------+---------------------+---------------------+-------------------+ 1 row in set (0.00 sec)最初の例では先頭から「**」が追加されていますが、 2番目の例では対象文字列が指定文字列長より短いので、先頭から文字列長分しか表示されません。
3番目の例では後ろから「**」が追加されていますが、 4番目の例では対象文字列が指定文字列長より短いので、先頭から文字列長分しか表示されません。
なお、対象文字列を空文字列にしてパディングを行うと、指定文字分の繰り返しが可能になります。 （そこまで必要性はありませんが）
それでは、マルチバイトセーフの例を示します。 （上記の例の文字列をマルチバイトに変更して実行）MariaDB [(none)]> SELECT -> LPAD('１２３', 5, '＊＊') -> ,LPAD('１２３', 2, '＊＊') -> ,RPAD('１２３', 5, '＊') -> ,RPAD('１２３', 2, '＊') -> ,RPAD('', 10, '＊') -> ; +---------------------------+---------------------------+-------------------------+-------------------------+----------------------+ | LPAD('１２３', 5, '＊＊') | LPAD('１２３', 2, '＊＊') | RPAD('１２３', 5, '＊') | RPAD('１２３', 2, '＊') | RPAD('', 10, '＊') | +---------------------------+---------------------------+-------------------------+-------------------------+----------------------+ | ＊＊１２３ | １２ | １２３＊＊ | １２ | ＊＊＊＊＊＊＊＊＊＊ | +---------------------------+---------------------------+-------------------------+-------------------------+----------------------+ 1 row in set (0.01 sec)
-
金額の数値データを表示する場合は、3桁ごとのカンマ編集で行うことが多いと思います。
この編集を行うために MySQL で数値を3桁ごとのカンマ区切り文字列に変換する方法として FORMAT 関数があります。
数値を '##,###,###.###' のような書式に変換し、小数点を丸めて、その結果を文字列として返します。
なお、 FORMAT 関数の説明は以下の通りです。
■[FORMAT関数]の説明
FORMAT(num, DP [, locale]) -- num : 指定数値 -- DP : 小数点の丸め位置（0 の場合は小数以下を表示しない） -- [locale] : 結果数の小数点、3 桁の区切り文字、および区切り文字間のグループ化に使用されるロケール -- ロケールの指定が無ければ'en_US' です。
それでは順次、FORMAT 関数の使用例を示します。
■[FORMAT関数]の例
MariaDB [(none)]> SELECT -> FORMAT(12345.123456, 4) -> ,FORMAT(12345.12, 4) -> ,FORMAT(12345.12, 0) -> ,FORMAT(12345.12, 4, 'de_DE') AS 'ドイツ語：ドイツ指定' -> ; +-------------------------+---------------------+---------------------+----------------------+ | FORMAT(12345.123456, 4) | FORMAT(12345.12, 4) | FORMAT(12345.12, 0) | ドイツ語：ドイツ指定 | +-------------------------+---------------------+---------------------+----------------------+ | 12,345.1235 | 12,345.1200 | 12,345 | 12.345,1200 | +-------------------------+---------------------+---------------------+----------------------+ 1 row in set (0.00 sec)最初の例では小数以下を4桁に丸めるために5桁の「5」を繰り上げいます。
また、2番目の例では小数点以下が4桁に満たないため、「0」で埋めて表示されます。
丸め位置を「0」に指定すると小数点以下が表示されません。
日本では3桁ごとの区切りを「,」(カンマ)で小数点を「.」（ピリオド）で表示しますが、 国によってはこれが逆になる場合があります。 （「de_DE」はドイツの場合を指定しています。）
ここで少し気になったので、最初の例の値をマイナスにしてみました。 結果を見ると「-12,345.1235」となり、絶対値の小数点5桁を四捨五入してその値にマイナスとしている様です。MariaDB [(none)]> SELECT FORMAT(-12345.123456, 4); +--------------------------+ | FORMAT(-12345.123456, 4) | +--------------------------+ | -12,345.1235 | +--------------------------+ 1 row in set (0.00 sec)
また、小数位置が「0」で小数点以下1位が「5」以上の場合はどうなるのでしょうか。
以下の結果から小数点以下1位が丸められます。MariaDB [(none)]> SELECT -> FORMAT(12345.5, 0) -> ,FORMAT(-12345.5, 0); +--------------------+---------------------+ | FORMAT(12345.5, 0) | FORMAT(-12345.5, 0) | +--------------------+---------------------+ | 12,346 | -12,346 | +--------------------+---------------------+ 1 row in set (0.00 sec)
