ITコーディネータのシュウです。

写真は、島根の実家の庭で咲かせているボタンの花です。以前、大根島（島根県松江市）の由志園に行ったときに購入したボタンを、父親が庭に植えていたのですが、今年もきれいに咲いたということで、父も喜んでいました。
また、先祖代々大事に育ててきた樹齢200年ほどの松の木があるのですが、毎年初夏に、新芽を摘む手入れをしているということで、父親が頑張って剪定作業を行っていました。今まであまり意識していなかったのですが、松の木の樹形を整えるのもなかなか大変だということを、改めて感じました。

＜本日の題材＞
バイト数分の表示と数値3桁ごとカンマ区切り表示（SQL Server）

データは日本語や英語、半角数値などが入り混じっている項目について、指定のバイト数のみを表示したいという場合や、数値項目を3桁ごとにカンマ区切りで表示させたい場合などが結構あるかと思いますが、本日は、それについて取り上げてみたいと思います。

例)
項目は、日本語や英語、半角数字などが混在していてバイト数と文字数が一致しない状態だけれども、帳票出力などで、どうしても限られたバイト数分しか表示したくないというケースがあるかと思います。そのときに、指定したバイト数分まで表示するSQLを確認してみます。

まず、今回確認するテーブルを作成し、データを登録します。
CREATE TABLE test_tbl(
ID INT,
NAME NVARCHAR(40),
QTY NUMERIC(12,2)
CONSTRAINT PK_test_tbl PRIMARY KEY (ID));

INSERT INTO test_tbl VALUES(1, 'テスト名称abcd_あいう', 1234567.12);
INSERT INTO test_tbl VALUES(2, 'テスト名称２_10xy', 20046);
INSERT INTO test_tbl VALUES(3, 'テスト名称３_データzyx', 33592.67);
INSERT INTO test_tbl VALUES(4, 'テスト名称4_全角半角混在', 4192.8);

それでは、作成したテーブルの「NAME」列について、15バイト分だけ抽出してみたいのですが、単純に最初の15文字だけをLEFT関数で抽出してみると、以下のようになります。

SELECT
ID,
left(NAME, 15) AS 名称,
QTY AS 数量
FROM test_tbl
ORDER BY ID;

これは、2バイト文字が混じっているので、明らかに15バイトよりも多いバイト数が抽出されます。
次に、convert関数を利用して抽出してみます。

SELECT
ID,
convert(varchar, substring(convert(text, NAME), 1, 15)) AS 名称,
QTY AS数量
FROM test_tbl
ORDER BY ID;

CONVERT関数にて、TEXT型に変換したものをSUBSTRING関数にて指定のバイト数を取り出し、さらに VARCHAR型に変換することで、指定バイト数分の表示ができるということです。
ちなみに、4行目の名称については、15バイト目がマルチバイト文字になるため、SQL Serverのほうでその前の文字までが表示されるかたちにしてくれます。

次に、上記の数量について、3桁ごとにカンマ区切りで表示させてみます。
このとき、数値を money型に変換した後、CONVERT関数の第3引数を1にすることで、3桁区切りの数値になります。

SELECT
ID,
convert(varchar, substring(convert(text, NAME), 1, 15)) AS 名称,
convert(nvarchar, convert(money, QTY), 1) AS 数量
FROM test_tbl
ORDER BY ID;

ただし、CONVERT関数でMONEY型に変換すると、小数点以下は2桁に四捨五入されるとともに、小数点以下2桁は必ず表示されるので、元の項目が整数であったとしても、’.00’が付いて表示されますので、注意が必要です。整数で表示したい場合は、’.00’を空白にREPLACEする必要があります。

今日は以上まで

にほんブログ村