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

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

MySQL で文字列の文字数を半角、および全角（マルチバイト文字）の区別無く、1文字は1文字としてカウントしたい場合があります。
今回はその取得方法としての CHAR_LENGTH 関数の使い方について説明します。
なお、この関数に関連して LENGTH 関数がありますが、こちらは内部的な文字列のコードのバイト数を返します。 文字セットにより文字コードのバイト数は異なりますので、表面上は同じ文字列でも LENGTH が返す値はことる場合があります。

■[CHAR_LENGTH]の例

SELECT CHAR_LENGTH('あいう'), LENGTH('あいう');

この SQL を実行すると多分「3」と「6」が表示されるはずだと思っていたのですが、 Windowsのコマンドプロンプトから MySQL にログインして SQL を実行すると以下の結果となりました。

C:\xampp\mysql\bin>mysql -u root -p
Enter password: ****
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 329
Server version: 10.1.19-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT CHAR_LENGTH('あいう'), LENGTH('あいう');
+-----------------------+------------------+
| CHAR_LENGTH('あいう') | LENGTH('あいう') |
+-----------------------+------------------+
|                     6 |                6 |
+-----------------------+------------------+
1 row in set (0.00 sec)

SQL を実行すると「6」と「6」が表示されてしまいました。

いろいろネットで調べてみると、SQL を実行しているWindwosコマンドプロンプトと データベース側の文字セットが合っていないのではと思い調べてみました。
先ずは現在の文字セットがどうなっているのかを以下の SQL コマンドで調べます。

SHOW VARIABLES LIKE '%char%';

このコマンドの実行結果は以下の様になります。

MariaDB [(none)]> SHOW VARIABLES LIKE '%char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8                           |
| character_set_connection | utf8                           |
| character_set_database   | utf8                           |
| character_set_filesystem | binary                         |
| character_set_results    | utf8                           |
| character_set_server     | utf8                           |
| character_set_system     | utf8                           |
| character_sets_dir       | C:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.02 sec)

クライアント側の文字セット（ character_set_client ）が utf8 ではまずいのではないかと思い、 Windowsなので Shift-JIS の識別子である cp932 にしてみることにしました。

■クライアント側の文字セットの変更[SET NAMES]の例

この設定に便利なコマンドがあるのですが、以下のものです。

SET NAMES charset_name [COLLATE collation_name];
-- charset_name   :クライアントのセッションの文字セット
-- collation_name :照合順序

早速、このコマンドを使って cp932 にしてみることにしました。

MariaDB [(none)]> SET NAMES cp932;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE '%char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | cp932                          |
| character_set_connection | cp932                          |
| character_set_database   | utf8                           |
| character_set_filesystem | binary                         |
| character_set_results    | cp932                          |
| character_set_server     | utf8                           |
| character_set_system     | utf8                           |
| character_sets_dir       | C:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

クライアント側の文字セット（ character_set_client ）が cp932 になりましたので CHAR_LENGTH のテストを行います。 期待通りに「3」の文字数が返されました。

MariaDB [(none)]> SELECT CHAR_LENGTH('あいう'), LENGTH('あいう');
+-----------------------+------------------+
| CHAR_LENGTH('あいう') | LENGTH('あいう') |
+-----------------------+------------------+
|                     3 |                6 |
+-----------------------+------------------+
1 row in set (0.00 sec)

なお phpMyAdmin で文字セットの確認と、上記の SQL を実行してみましたが、以下の様な表示となりました。

character_set_client が utf8mb4 となっているので 'あいう' のそれぞれのコードは内部的に3バイトのため、 LENGTH の結果は「9」が返ってきます。