MySQL｜VB.NET-TIPS などプログラミングについて

■MySQL

[2024/01/23] MySQL 文字列中の指定文字列の置き換え方法について（REPLACE） (No.345)
[2024/01/23] MySQL 文字列を区切り文字を使用した連結の方法について（CONCAT_WS） (No.344)
[2024/01/22] MySQL 文字列の前後に指定桁数分の追加文字列を追加する方法について（LPAD、RPAD） (No.343)
[2024/01/22] MySQL 数値を3桁ごとのカンマ区切り文字列に変換する方法について（FORMAT） (No.342)
[2024/01/22] MySQL 文字列から空白または指定した文字列を取り除く方法について（TRIM、LTRIM、RTRIM） (No.341)

≪ [1] [2] [3] [4] [5] ≫

2024/01/23

MySQL 文字列中の指定文字列の置き換え方法について（REPLACE）

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
0CM

2024/01/23

MySQL 文字列を区切り文字を使用した連結の方法について（CONCAT_WS）

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
0CM

2024/01/22

MySQL 文字列の前後に指定桁数分の追加文字列を追加する方法について（LPAD、RPAD）

表示したい文字列の前後に何かの文字列を追加したい場合はたまにあると思います。例えば、空白を埋めたりする場合です。

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)

MySQL
0CM

2024/01/22

MySQL 数値を3桁ごとのカンマ区切り文字列に変換する方法について（FORMAT）

金額の数値データを表示する場合は、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
0CM

2024/01/22

MySQL 文字列から空白または指定した文字列を取り除く方法について（TRIM、LTRIM、RTRIM）

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
0CM