-
表示したい文字列の前後に何かの文字列を追加したい場合はたまにあると思います。 例えば、空白を埋めたりする場合です。
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)
PR -
金額の数値データを表示する場合は、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)
-
MySQL で文字列から空白または指定した文字列を取り除く方法として TRIM LTRIM RTRIM 関数があります。
この関数はマルチバイトセーフなので、全角文字も1文字として扱われます。
なお、 TRIM 関数の説明は以下の通りです。
■[TRIM関数]の説明
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) -- {BOTH | LEADING | TRAILING} : TRIMする方法を指定する。 -- BOTH : 先頭からと最後尾からの両方からTRIM -- LEADING : 先頭からのTRIM -- TRAILING : 最後尾からのTRIM -- [remstr] : 削除対象文字列 -- str : TRIM対象文字列 TRIM([remstr FROM] str)
それでは順次、TRIM 関数の使用例を示します。
■[TRIM関数]の例
MariaDB [(none)]> SELECT -> TRIM(' ABC ') AS '一般的なTRIM' -> ,TRIM(BOTH '-' FROM '--ABC--') AS '指定文字列TRIM:BOTH' -> ,TRIM(LEADING '-' FROM '--ABC--') AS '指定文字列TRIM:LEADING' -> ,TRIM(TRAILING '-' FROM '--ABC--') AS '指定文字列TRIM:TRAILING' -> ; +--------------+---------------------+------------------------+-------------------------+ | 一般的なTRIM | 指定文字列TRIM:BOTH | 指定文字列TRIM:LEADING | 指定文字列TRIM:TRAILING | +--------------+---------------------+------------------------+-------------------------+ | ABC | ABC | ABC-- | --ABC | +--------------+---------------------+------------------------+-------------------------+ 1 row in set (0.03 sec)確かに指定文字列を TRIM 処理されるのが分かります。
また、この関数はマルチバイトセーフなので、指定文字列を全て全角の文字にしてテストしてみます。MariaDB [(none)]> SELECT -> TRIM(' あいう ') AS '一般的なTRIM' -> ,TRIM(BOTH 'あ' FROM 'ああＡＢＣああ') AS '指定文字列TRIM:BOTH' -> ,TRIM(LEADING 'あ' FROM 'ああＡＢＣああ') AS '指定文字列TRIM:LEADING' -> ,TRIM(TRAILING 'あ' FROM 'ああＡＢＣああ') AS '指定文字列TRIM:TRAILING' -> ; +--------------+---------------------+------------------------+-------------------------+ | 一般的なTRIM | 指定文字列TRIM:BOTH | 指定文字列TRIM:LEADING | 指定文字列TRIM:TRAILING | +--------------+---------------------+------------------------+-------------------------+ | あいう | ＡＢＣ | ＡＢＣああ | ああＡＢＣ | +--------------+---------------------+------------------------+-------------------------+ 1 row in set (0.01 sec)
最初の TRIM では全角空白は取除けないので、以下の様に BOTH 指定で全角空白を指定します。MariaDB [(none)]> SELECT TRIM(BOTH ' ' FROM ' あいう '); +-----------------------------------+ | TRIM(BOTH ' ' FROM ' あいう ') | +-----------------------------------+ | あいう | +-----------------------------------+ 1 row in set (0.00 sec)
TRIM 対象文字列 '△あいう△'（空白を△）から空白が削除されています。
なお、BOTH 指定が無くても、以下の様に BOTH 指定と同様の処理となります。MariaDB [(none)]> SELECT TRIM(BOTH ' ' FROM ' あいう '); +-----------------------------------+ | TRIM(BOTH ' ' FROM ' あいう ') | +-----------------------------------+ | あいう | +-----------------------------------+ 1 row in set (0.00 sec)
RTRIM 関数に関連してですが、文字列の空白を前後から削除する専門の関数として LTRIM RTRIM 関数があります。
LTRIM は文字列の先頭から、RTRIM は文字列の最後から空白を削除します。以下に例を示します。 （なお、これらの関数もマルチバイトセーフです。）■[LTRIM関数][RTRIM関数]の例
MariaDB [(none)]> SELECT -> LTRIM(' ABC ') -> ,RTRIM(' ABC ') -> ,LTRIM(' あいう ') -> ,RTRIM(' あいう ') -> ; +------------------+------------------+---------------------+---------------------+ | LTRIM(' ABC ') | RTRIM(' ABC ') | LTRIM(' あいう ') | RTRIM(' あいう ') | +------------------+------------------+---------------------+---------------------+ | ABC | ABC | あいう | あいう | +------------------+------------------+---------------------+---------------------+ 1 row in set (0.00 sec)TRIM された結果になっていると思うのですが、取り敢えず TRIM 結果に文字列を連結して確かめます。
MariaDB [(none)]> SELECT -> CONCAT(LTRIM(' ABC '), '/') -> ,CONCAT(RTRIM(' ABC '), '/') -> ,CONCAT(LTRIM(' あいう '), '/') -> ,CONCAT(RTRIM(' あいう '), '/') -> ; +-------------------------------+-------------------------------+----------------------------------+----------------------------------+ | CONCAT(LTRIM(' ABC '), '/') | CONCAT(RTRIM(' ABC '), '/') | CONCAT(LTRIM(' あいう '), '/') | CONCAT(RTRIM(' あいう '), '/') | +-------------------------------+-------------------------------+----------------------------------+----------------------------------+ | ABC / | ABC/ | あいう / | あいう/ | +-------------------------------+-------------------------------+----------------------------------+----------------------------------+ 1 row in set (0.03 sec)
-
MySQL で文字列の連結を行う方法として CONCAT 関数があります。
CONCAT 関数は以下の様な引数をとります。CONCAT(str1, str2, str3, ...) -- 文字列 str1, str2, str3 と指定されたものを連結した文字列を返し、 -- 引数に NULL が含まれている場合は NULL を返します。 -- なお、引数に数値が指定された場合は、文字列に変換されて連結されます。
オラクルでは CONCAT 関数の引数は2個までしかないのですが、MySQL では2個以上連結が可能です。 （もっともオラクルでは「||」で連結すれば同じ事ができます）
それでは、CONCAT 関数の使用例を示します。
■[CONCAT関数]の例
MariaDB [(none)]> SELECT -> CONCAT('ABC', 'DEFG', 'あいう', 'HI') AS '文字列の連結' -> ,CONCAT('ABC', 100, '全角') AS 'リテラル数値を連結' -> ,CONCAT(100, 200) AS 'リテラル数値のみを連結' -> ,CONCAT('ABC', NULL, '全角') AS 'NULLを連結' -> ; +-----------------+--------------------+------------------------+------------+ | 文字列の連結 | リテラル数値を連結 | リテラル数値のみを連結 | NULLを連結 | +-----------------+--------------------+------------------------+------------+ | ABCDEFGあいうHI | ABC100全角 | 100200 | NULL | +-----------------+--------------------+------------------------+------------+ 1 row in set (0.00 sec)確かに数値の場合も文字列に変換されて処理されるのが分かります。
また、引数の中に NULL が存在する場合は NULL が返されます。
上記の例では、全てリテラルの文字列を引数に指定しましたが、 リテラルであればそれらを単に羅列して SQL に記述すれば連結する様です。
■[文字列を並べて配置]の例
MariaDB [(none)]> SELECT 'ABC' 'DEFG' 'あいう' 'HI' AS '文字列の連結'; +-----------------+ | 文字列の連結 | +-----------------+ | ABCDEFGあいうHI | +-----------------+ 1 row in set (0.00 sec)
確かに 文字列が連結されていますが、これにそこまでの有用性は無いかもしれません。
■[CONCAT関数]をテーブルデータを引数にする例
テーブルデータを引数に指定するために、データベースを指定し「t_auto」なるテストテーブルの一覧を取得します。
MariaDB [(none)]> USE test; Database changed 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(id, '-', val) FROM t_auto; +----------------------+ | CONCAT(id, '-', val) | +----------------------+ | 1-test1 | | 2-test2 | | 4-test4 | | 5-test-a | | 6-あいう | +----------------------+ 5 rows in set (0.00 sec)
テーブルデータの場合は上記の文字列の連続配置の様にはできなくて（オラクルの連結子「||」の様にはいかないので） CONCAT 関数の出番となります。
-
MySQL で文字列から部分文字列を取得する方法として、 LEFT , RIGHT , SUBSTRING 関数があります。
LEFT , RIGHT 関数は指定された文字列の左側および右側から切出し文字数を指定し部分文字列を取得します。
また、SUBSTRING 関数は指定された文字列の切出し開始位置と切出し文字数を指定し、部分文字列を取得します。
これらの関数はマルチバイトセーフなので、全角文字も1文字として扱われます。
なお、 LEFT RIGHT 関数の説明は以下の通りです。
LEFT(str, len) -- 文字列 str から左側から len 文字分の文字列を返し、引数が NULL である場合は NULL を返します。 RIGHT(str, len) -- 文字列 str から右側から len 文字分の文字列を返し、引数が NULL である場合は NULL を返します。 -- ※SUBSTRINGは4つの形式があります SUBSTRING(str,pos) SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) SUBSTRING(str FROM pos FOR len) -- len 引数を付けない形式では、位置 pos で始まる文字列 str からの部分文字列が返されます。 -- len 引数を付けた形式では、位置 pos で始まる文字列 str からの部分文字列 len 文字長が返されます。 -- FROM を使用する形式は、標準の SQL 構文です。 -- また、pos に負の値を使用することもできます。 -- その場合、部分文字列の先頭は文字列の先頭でなく、文字列の末尾からの pos 文字になります。 -- この関数のどの形式でも、pos で負の値を使用できます。 pos の値が 0 の場合、空の文字列が返されます。 -- （すべての形式の SUBSTRING() で、部分文字列の抽出が開始される文字列内の最初の文字の位置が 1 とみなされます。） -- 「https://dev.mysql.com/doc/refman/8.0/ja/string-functions.html からの抜粋です。」
それでは順次、LEFT, RIGHT, SUBSTRING 関数の使用例を示します。
■[LEFT関数]の例
MariaDB [(none)]> SELECT -> LEFT('ABCDEFGH', 2) -> ,LEFT('ABCDEFGH', 10) -> ,LEFT('日本語テスト', 3) -> ,LEFT('日本語テスト', 10) -> ,LEFT(NULL, 10) -> ,LEFT('ABCDEFGH', 0) -> ; +---------------------+----------------------+-------------------------+--------------------------+----------------+---------------------+ | LEFT('ABCDEFGH', 2) | LEFT('ABCDEFGH', 10) | LEFT('日本語テスト', 3) | LEFT('日本語テスト', 10) | LEFT(NULL, 10) | LEFT('ABCDEFGH', 0) | +---------------------+----------------------+-------------------------+--------------------------+----------------+---------------------+ | AB | ABCDEFGH | 日本語 | 日本語テスト | NULL | | +---------------------+----------------------+-------------------------+--------------------------+----------------+---------------------+ 1 row in set (0.00 sec)確かにマルチバイトの文字も1文字として処理されるのが分かります。
また、文字数が文字列より大きい値が指定されてた場合は指定文字列全てが返されます。
なお、文字数指定が「0」以下の場合は長さ「0」の文字列が返されるのが分かります。
■[RIGHT関数]の例
MariaDB [(none)]> SELECT -> RIGHT('ABCDEFGH', 2) -> ,RIGHT('ABCDEFGH', 10) -> ,RIGHT('日本語テスト', 3) -> ,RIGHT('日本語テスト', 10) -> ,RIGHT(NULL, 10) -> ,RIGHT('ABCDEFGH', 0) -> ; +----------------------+-----------------------+--------------------------+---------------------------+-----------------+----------------------+ | RIGHT('ABCDEFGH', 2) | RIGHT('ABCDEFGH', 10) | RIGHT('日本語テスト', 3) | RIGHT('日本語テスト', 10) | RIGHT(NULL, 10) | RIGHT('ABCDEFGH',0) | +----------------------+-----------------------+--------------------------+---------------------------+-----------------+----------------------+ | GH | ABCDEFGH | テスト | 日本語テスト | NULL | | +----------------------+-----------------------+--------------------------+---------------------------+-----------------+----------------------+ 1 row in set (0.02 sec)確かに LEFT 関数の処理を右側から行った結果になっています。
■[SUBSTRING関数]の例
MariaDB [(none)]> SELECT -> SUBSTRING('ABCDEFGHIJ', 6) AS '先頭から6番目以降全て' -> ,SUBSTRING('ABCDEFGHIJ' FROM 4) AS '先頭から6番目以降全て' -> ,SUBSTRING('ABCDEFGHIJ', 3, 6) AS '先頭から3番目から6文字' -> ,SUBSTRING('ABCDEFGHIJ', -3) AS '最後から3番目以降全て' -> ,SUBSTRING('ABCDEFGHIJ', -5, 3) AS '最後から5番目から3文字' -> ,SUBSTRING('ABCDEFGHIJ' FROM -4 FOR 2) AS '最後から4番目から2文字' -> ; +-----------------------+-----------------------+------------------------+-----------------------+------------------------+------------------------+ | 先頭から6番目以降全て | 先頭から6番目以降全て | 先頭から3番目から6文字 | 最後から3番目以降全て | 最後から5番目から3文字 | 最後から4番目から2文字 | +-----------------------+-----------------------+------------------------+-----------------------+------------------------+------------------------+ | FGHIJ | DEFGHIJ | CDEFGH | HIJ | FGH | GH | +-----------------------+-----------------------+------------------------+-----------------------+------------------------+------------------------+ 1 row in set (0.00 sec)
