いつもお世話になっております。
RfromL.comです。
SQLServerで文字列を指定桁数で0埋めするFORMAT関数についてです。
1.はじめに
数値の頭（左側）を指定桁数になるまで0（ゼロ）で埋めることをゼロパディングといいます。
今回はSQLserverのテーブルデータのうち、数値部分を指定桁数でゼロパディングするformat関数についてです。
なお、記事作成にあたって使用した環境は以下のバージョンとなっています。
※format関数はSQLserver2012からの機能なのでそれ以前のバージョンでは使用できません。
| OS | Windows11 Home 64ビット |
| DBMS | SQL Server 2019 |
| 統合環境 | SQL Server Management Studio v18.10 |
2.formatの構文
format関数の構文は以下の通りです。
FORMAT(数値,桁数)数値部分には、フォーマット対象の数値や、テーブルの項目名を指定しますが桁数部分には２パターンあります。
以降でそれぞれ桁数5桁で0埋めする条件として例を示します。
2-1.通常指定
指定桁数分の 0（ゼロ）を入力するパターンです。
FORMAT(数値,'00000')上記例では、数値を5桁の0（ゼロ）埋めで表示します。
4桁の0（ゼロ）埋めする場合は、’0000’を入力します。
実際に値を指定して実行
2-2.書式指定子
書式指定子「D」と桁数「5」（今回は5桁）を合わせて「D5」を入力するパターンです。
3.テスト環境
テスト用のテーブルとデータを用意して、実際にテーブルからSELECTしてみます。
3-1.テーブル
テスト用に型の異なる項目を持つテーブルを作成しました。
データ型は主だった型を用意しました。
分かりやすいように項目名にはデータ型の名前を設定しています。
[テーブル定義]
| No | 項目名 | データ型 |
|---|---|---|
| 1 | 連番 | int |
| 2 | BIGINT型 | bigint |
| 3 | INT型 | int |
| 4 | SMALLINT型 | smallint |
| 5 | TINYINT型 | tinyint |
| 6 | BIT型 | bit |
| 7 | DECIMAL型 | decimal |
| 8 | NUMERIC型 | numeric |
| 9 | MONEY型 | money |
| 10 | SMALLMONEY型 | smallmoney |
| 11 | FLOAT型 | real |
| 12 | REAL型 | real |
| 13 | DATETIME型 | datetime |
| 14 | SMALLDATETIME型 | smalldatetime |
| 15 | CHAR型 | char |
| 16 | VARCHAR型 | varchar |
| 17 | TEXT型 | text |
| 18 | NCHAR型 | nchar |
| 19 | NVARCHAR型 | nvarchar |
| 20 | NTEXT型 | ntext |
[テーブルクリエイト文]
create table DATETYPE (
[連番] [int] NOT NULL,
[BIGINT型] [bigint] NOT NULL,
[INT型] [int] NOT NULL,
[SMALLINT型] [smallint] NOT NULL,
[TINYINT型] [tinyint] NOT NULL,
[BIT型] [bit] NOT NULL,
[DECIMAL型] [decimal] (10) NOT NULL,
[NUMERIC型] [numeric] (10) NOT NULL,
[MONEY型] [money] NOT NULL,
[SMALLMONEY型] [smallmoney] NOT NULL,
[FLOAT型] [float] (10) NOT NULL,
[REAL型] [real] NOT NULL,
[DATETIME型] [datetime] NOT NULL,
[SMALLDATETIME型] [smalldatetime] NOT NULL,
[CHAR型] [char] (10) NOT NULL,
[VARCHAR型] [varchar] (10) NOT NULL,
[TEXT型] [text] NOT NULL,
[NCHAR型] [nchar] (10) NOT NULL,
[NVARCHAR型] [nvarchar] (10) NOT NULL,
[NTEXT型] [ntext] NOT NULL
PRIMARY KEY([連番])
);3-2.データ
[データインサート文]
insert into DATETYPE values('1','2','3','4','5','0','6', '7', '8.00', '9.00','10','11','1900-01-12 00:00:00.000','1900-01-13 00:00:00','14 ','15','17','18 ','19','20');
insert into DATETYPE values('2','3','4','5','6','1','7', '8', '9.00','10.00','11','12','1900-01-13 00:00:00.000','1900-01-14 00:00:00','15 ','16','18','19 ','20','21');
insert into DATETYPE values('3','4','5','6','7','0','8', '9','10.00','11.00','12','13','1900-01-14 00:00:00.000','1900-01-15 00:00:00','16 ','17','19','20 ','21','22');
insert into DATETYPE values('4','5','6','7','8','1','9','10','11.00','12.00','13','14','1900-01-15 00:00:00.000','1900-01-16 00:00:00','17 ','18','20','21 ','22','23');4.実行
用意したテーブルに対して実際にFormat関数を使用したSELECT文を実行します。
4-1.「通常指定」実行
[ SQL文 ]
select 連番
,format(BIGINT型 ,'00000') as BIGINT型
,format(INT型 ,'00000') as INT型
,format(SMALLINT型 ,'00000') as SMALLINT型
,format(TINYINT型 ,'00000') as TINYINT型
-- ,format(BIT型 ,'00000') as BIT型 -- Errorになる
,format(DECIMAL型 ,'00000') as DECIMAL型
,format(NUMERIC型 ,'00000') as NUMERIC型
,format(MONEY型 ,'00000') as MONEY型
,format(SMALLMONEY型 ,'00000') as SMALLMONEY型
,format(FLOAT型 ,'00000') as FLOAT型
,format(REAL型 ,'00000') as REAL型
,format(DATETIME型 ,'00000') as DATETIME型
,format(SMALLDATETIME型,'00000') as SMALLDATETIME型
-- ,format(CHAR型 ,'00000') as CHAR型 -- Errorになる
-- ,format(VARCHAR型 ,'00000') as VARCHAR型 -- Errorになる
-- ,format(TEXT型 ,'00000') as TEXT型 -- Errorになる
-- ,format(NCHAR型 ,'00000') as NCHAR型 -- Errorになる
-- ,format(NVARCHAR型 ,'00000') as NVARCHAR型 -- Errorになる
-- ,format(NTEXT型 ,'00000') as NTEXT型 -- Errorになる
from DATETYPE;※実行するとエラーになるデータ型の項目部分はコメントアウトしています。
[ 実行結果 ]
実行した結果、正常に機能する型、機能しない型、エラーになる型をまとめると以下の通りです。
[正常に機能する型]
・BIGINT
・INT
・SMALLINT
・TINYINT
・DECIMAL
・NUMERIC
・MONEY
・SMALLMONEY
・FLOAT
・REAL
[機能しない型]
・DATETIME
・SMALLDATETIME
[エラーになる型]
・BIT
・CHAR
・VARCHAR
・TEXT
・NCHAR
・NVARCHAR
・NTEXT
4-2.「書式指定子」実行
[ SQL文 ]
select 連番
,format(BIGINT型 ,'D5') as BIGINT型
,format(INT型 ,'D5') as INT型
,format(SMALLINT型 ,'D5') as SMALLINT型
,format(TINYINT型 ,'D5') as TINYINT型
-- ,format(BIT型 ,'D5') as BIT型 -- Errorになる
,format(DECIMAL型 ,'D5') as DECIMAL型
,format(NUMERIC型 ,'D5') as NUMERIC型
,format(MONEY型 ,'D5') as MONEY型
,format(SMALLMONEY型 ,'D5') as SMALLMONEY型
,format(FLOAT型 ,'D5') as FLOAT型
,format(REAL型 ,'D5') as REAL型
,format(DATETIME型 ,'D5') as DATETIME型
,format(SMALLDATETIME型 ,'D5') as SMALLDATETIME型
-- ,format(CHAR型 ,'D5') as CHAR型 -- Errorになる
-- ,format(VARCHAR型 ,'D5') as VARCHAR型 -- Errorになる
-- ,format(TEXT型 ,'D5') as TEXT型 -- Errorになる
-- ,format(NCHAR型 ,'D5') as NCHAR型 -- Errorになる
-- ,format(NVARCHAR型 ,'D5') as NVARCHAR型 -- Errorになる
-- ,format(NTEXT型 ,'D5') as NTEXT型 -- Errorになる
from DATETYPE;※実行するとエラーになるデータ型の項目部分はコメントアウトしています。
[ 実行結果 ]
実行した結果、正常に機能する型、機能しない型、エラーになる型をまとめると以下の通りです。
[ 正常に機能する型 ]
・BIGINT
・INT
・SMALLINT
・TINYINT
[機能しない型]
・DECIMAL
・NUMERIC
・MONEY
・SMALLMONEY
・FLOAT
・REAL
・DATETIME
・SMALLDATETIME
[エラーになる型]
・BIT
・CHAR
・VARCHAR
・TEXT
・NCHAR
・NVARCHAR
・NTEXT
5.おわりに
指定する桁数が多くなればなるほど書式指定子を使った方がコード自体はすっきりします。
ですが、正常に機能する項目が整数型の４つだけで、違う型に使用するごとに表記を変えるのは面倒なので通常の指定を使った方が統一できるかなという印象です。
以上です。
宜しくお願い致します。
