SQL Serverで棚札の表示を整えると、元の札の末尾にあった半角スペースや、補助札の記録状態が見た目から分かりにくくなります。文字数とデータの大きさを表すバイト数を並べ、ハイフンの位置や補助札の状態を別の列に出すと、整形前の違いをたどれます。
練習用のSQL Serverと、データベースに接続して結果を確認するアプリのSQL Server Management Studio(SSMS)を用意します。SQL Server 2022で検証した架空の棚札の4例を使い、目的に合う関数を選び、入力を1か所変えて元に戻した結果まで確かめます。
先に結論
- LENで末尾の半角スペースを除いた文字数を数え、DATALENGTHで末尾も含むバイト数を確認します。両端の半角スペースは基本形のTRIMで取り除けます。
- CHARINDEXで最初のハイフンの位置を調べ、SUBSTRINGでその手前を取り出します。位置が0ならハイフンが見つからなかったと判断できます。
- NULLは値が入っていない状態で、空文字は文字を一つも含まない値です。CONCATでつないだ表示が同じでも、補助札のバイト数は未記録ならNULL、空文字なら0です。
SSMSで練習用のSQLを実行する
掲載結果は、ローカルのSQL Server 2022(16.00.1000)の作業用データベースtempdbで、架空の棚札を使って確認したものです。SSMSの画面操作は公式資料に沿っています。
SQLはデータベースに処理を指示する言語です。掲載例のSELECTは値や計算結果を表示し、ASは結果に出す列に名前を付けるときに使います。結果を照合するときは、まずこの列名を手掛かりにします。
- SSMSで練習用のSQL Serverに接続し、接続先を確認してから「新しいクエリ」を選びます。クエリは、ここでは実行するSQLの文を指します。
- コード枠を1つ選び、先頭から末尾までクエリの入力欄に貼り付けます。入力欄でSQLの一部が選択されていると、その範囲だけが実行されます。入力欄内の選択を解除してから「実行」を選びます。
- 画面下部の「結果」で、列名と値を掲載結果と照合します。エラーが出た場合は「メッセージ」を読み、貼り付けた範囲と接続先を確認します。
- 次の例に進むときは、入力欄のSQLを次のコード枠の内容に置き換えます。掲載結果の枠は照合に使います。確認が済んだらクエリのタブを閉じます。
掲載結果では、文字列の端が分かるように単一引用符で囲んでいます。引用符の間が空なら空文字です。SSMSの結果一覧では文字列に引用符が自動で付かないため、空欄が気になったら、バイト数や記録状態を示す列を確認します。
棚札をどう整えたいかで関数を選ぶ
文字列は、棚札の「北-07」のように、文字をひとまとまりにした値です。関数は、渡された値を決まった方法で処理し、結果を返す仕組みです。関数に渡す値を引数と呼びます。
表の「やりたいこと」から目的に合う行を選べます。たとえば余分なスペースが気になるときは、「文字を数える」と「バイト数を測る」の2行を見比べると、確認する値が分かります。
| やりたいこと | 使う関数 | 結果の確認 |
|---|---|---|
| 文字を数える | LEN | 末尾の半角スペースを数えない |
| バイト数を測る | DATALENGTH | 末尾スペースも含む。入力の型で変わる |
| 両端を整える | TRIM | 既定で半角スペースを取り除く |
| 区切りの前を取り出す | CHARINDEX と SUBSTRING | 位置が0なら区切りなし |
| 値をつなぐ | CONCAT | NULLと空文字は結合表示で同じになる |
末尾の半角スペースを文字数とバイト数で確かめる
この棚札は、TRIMで両端の半角スペースを取り除くと14バイトから8バイトになります。
DECLAREで値を一時的に入れておく変数を用意します。この例の変数名は@tagです。nvarcharは、さまざまな言語の文字を扱えるUnicodeの文字列型です。文字列を囲む単一引用符の前のNも、その値をUnicodeの文字列として指定する印です。
nvarchar(12)の12は、変数に入れられる文字列の容量を指定する数字です。2バイトを1組として、最大12組を使えます。この棚札の文字は1文字で1組を使いますが、文字によっては1文字で2組を使います。
CONCATで棚札の両端に角かっこを添えると、半角スペースの位置が見やすくなります。LENで数えた文字数とDATALENGTHで測ったバイト数を並べ、両端を整えた後の値も出します。バイト数は文字列の型や文字の表し方で変わります。
札の文字数とバイト数を並べる
DECLARE @tag nvarchar(12) = N' 北-07 ';
SELECT CONCAT(N'[', @tag, N']') AS raw_tag,
LEN(@tag) AS len_chars,
DATALENGTH(@tag) AS raw_bytes,
CONCAT(N'[', TRIM(@tag), N']') AS trimmed_tag,
DATALENGTH(TRIM(@tag)) AS trimmed_bytes;
SELECT DATALENGTH(TRIM(NCHAR(9))) AS tab_bytes,
DATALENGTH(TRIM(NCHAR(12288))) AS wide_space_bytes;
実行結果
raw_tag | len_chars | raw_bytes | trimmed_tag | trimmed_bytes
'[ 北-07 ]' | 5 | 14 | '[北-07]' | 8
tab_bytes | wide_space_bytes
2 | 2
最初の結果のraw_tagでは、角かっこの内側に先頭1個と末尾2個の半角スペースがあります。len_charsの5には先頭の1個が含まれます。raw_bytesの14は、入力の7文字がそれぞれ2バイトだからです。TRIM後のtrimmed_tagは「[北-07]」で、trimmed_bytesは8です。
次の結果では、NCHARで文字番号を指定して作ったタブ(9)と全角スペース(12288)を調べています。tab_bytesとwide_space_bytesはいずれも2です。基本形のTRIMが両端から取り除くのは半角スペースなので、この2つの文字は残ります。
基本形のTRIMはSQL Server 2017以降で使えます。それより前の版で両端を整えるには、末尾のスペースを取り除くRTRIMの結果を、先頭のスペースを取り除くLTRIMに渡します。
SQL Server 2022で取り除く方向を指定する拡張書式には、互換性レベル160が必要です。互換性レベルはデータベースごとにSQLの一部の動作を決める設定です。掲載例は方向を指定しない基本形です。
最初のハイフンより前を取り出す
ハイフンが見つかったら、その位置から1を引いた数だけ先頭から取り出します。
番号と棚札を組にした練習データを5行用意します。FROMは読み取るデータを示し、その中のVALUESが5行を作ります。ASでデータのまとまりと列に名前を付け、ORDER BYで番号順に表示します。
CHARINDEXには、探す文字、探す先の順に値を渡します。位置は先頭から1で数え、見つからなければ0、元の札が未記録ならNULLが返ります。SUBSTRINGには、元の札、取り出しの開始位置、取り出す文字数の順に値を渡します。SQL Server 2022ではこの文字数の指定が必要です。
ハイフンの位置と先頭部分を確認する
SELECT sample_no, tag, CHARINDEX(N'-', tag) AS dash_pos,
SUBSTRING(tag, 1, NULLIF(CHARINDEX(N'-', tag), 0) - 1) AS area,
CASE
WHEN tag IS NULL THEN N'未記録'
WHEN CHARINDEX(N'-', tag) = 0 THEN N'区切りなし'
WHEN CHARINDEX(N'-', tag) = 1 THEN N'区切りが先頭'
ELSE N'先頭部分あり'
END AS tag_state
FROM (VALUES (1, N'北-07'), (2, N'北07'), (3, NULL),
(4, N'-07'), (5, N'北-07-青')) AS samples(sample_no, tag)
ORDER BY sample_no;
実行結果
sample_no | tag | dash_pos | area | tag_state
1 | '北-07' | 2 | '北' | '先頭部分あり'
2 | '北07' | 0 | NULL | '区切りなし'
3 | NULL | NULL | NULL | '未記録'
4 | '-07' | 1 | '' | '区切りが先頭'
5 | '北-07-青' | 2 | '北' | '先頭部分あり'
1番はdash_posが2なので、areaには先頭の1文字「北」が入ります。2番はdash_posが0です。NULLIFで0をNULLに変えてから1を引くと、SUBSTRINGに渡す文字数もNULLになります。負の文字数を渡さずに済み、areaはNULLになります。
札が未記録の3番は、dash_posとareaがどちらもNULLです。4番はハイフンが先頭にあり、取り出す文字数が0なのでareaは空文字です。5番はハイフンが2個あり、最初の位置を使うためareaは「北」です。
CASEはこの例の条件を上から調べ、最初に当てはまった条件の状態名をtag_stateに出します。IS NULLで元の札に値が入っているかを調べるため、areaが同じNULLでも、2番は「区切りなし」、3番は「未記録」と区別できます。
連結後も補助札の未記録と空文字を見分ける
CONCATに棚札、区切り、補助札の順で値を渡して、表示用の札を作ります。CONCATはNULLを空文字として扱います。スラッシュと前後の半角スペースを区切りとして渡しているので、補助札が未記録でも区切りは表示に残ります。
連結後も補助札の記録状態を残す
SELECT sample_no, DATALENGTH(note) AS note_bytes,
CONCAT(N'棚-07', N' / ', note) AS display_tag,
CASE WHEN note IS NULL THEN N'未記録'
WHEN DATALENGTH(note) = 0 THEN N'空文字'
ELSE N'記録あり'
END AS note_state
FROM (VALUES (1, N'青'), (2, NULL), (3, N'')) AS samples(sample_no, note)
ORDER BY sample_no;
実行結果
sample_no | note_bytes | display_tag | note_state
1 | 2 | '棚-07 / 青' | '記録あり'
2 | NULL | '棚-07 / ' | '未記録'
3 | 0 | '棚-07 / ' | '空文字'
1番のdisplay_tagには補助札の「青」まで表示され、元の補助札のバイト数を示すnote_bytesは2です。2番と3番のdisplay_tagは同じなので、note_bytesとnote_stateを見ます。2番はNULLで「未記録」、3番は0で「空文字」です。
決まった位置から取り出して表記をそろえる
棚札の書式が決まっている場合は、LEFTで左端から、RIGHTで右端から必要な部分を取り出せます。どちらも最初に元の札、次に取り出す文字数を指定します。
区切りを置き換えるREPLACEには、元の札、探す文字、置き換える文字の順に値を渡します。英字を大文字にそろえるときはUPPER、小文字にそろえるときはLOWERを使います。
位置が決まった札を抜き出し、表示をそろえる
SELECT LEFT(N'ab-07', 2) AS left_part,
RIGHT(N'ab-07', 2) AS right_part,
REPLACE(N'ab-07-青', N'-', N'/') AS with_slash,
UPPER(N'ab-07') AS upper_tag,
LOWER(N'AB-07') AS lower_tag;
実行結果
left_part | right_part | with_slash | upper_tag | lower_tag
'ab' | '07' | 'ab/07/青' | 'AB-07' | 'ab-07'
left_partは左から2文字の「ab」、right_partは右から2文字の「07」です。with_slashはハイフン2個をどちらも置き換えた「ab/07/青」です。upper_tagは「AB-07」、lower_tagは「ab-07」になり、英字の表記をそれぞれの列で確かめられます。
2番の棚札を変えて元の結果に戻す
クエリの入力欄で2番の練習用の札だけを変えると、ハイフンの有無がどの列に表れるかを確認できます。2番の行で見るのは、位置を示すdash_pos、取り出した文字を示すarea、札の状態を示すtag_stateの3列です。
- SSMSで「新しいクエリ」を選び、「ハイフンの位置と先頭部分を確認する」のコード枠全体を貼り付けて実行します。2番の3列は順に「0」「NULL」「区切りなし」です。
- 入力欄のVALUESで、2番の札だけを「北07」から「北-07」に変えます。札の前のNと単一引用符はそのままにして全体を実行します。2番の3列は「2」「北」「先頭部分あり」になります。
- 同じ入力欄で2番の札を「北07」に戻し、枠の先頭から末尾まで再実行します。2番の3列が「0」「NULL」「区切りなし」に戻れば、元の入力で同じ結果が得られています。
