nvarcharの列の照合順序を変えて文字数をカウントしてみる
環境
code:select.sql
select @@VERSION;
table:result
(列名なし)
確認用テーブルの準備
code:create.sql
vc varchar (max)
, nvc nvarchar(max)
-- varcharからnvarcharに変えてみる
, altvc varchar (max)
-- 照合順序をJapanese_Bushu_Kakusu_100_CI_AS_SCに設定してみる
, kakusu_nvc nvarchar(max)
, text nvarchar(max)
);
kakusu_nvcの照合順序を変える
code:alter.sql
ALTER TABLE #T ALTER COLUMN altvc nvarchar(max); ALTER TABLE #T ALTER COLUMN kakusu_nvc nvarchar(max) COLLATE Japanese_Bushu_Kakusu_100_CI_AS_SC; code:select_INFORMATION_SCHEMA.sql
SELECT
COLUMN_NAME AS column_name,
DATA_TYPE AS type,
CHARACTER_SET_NAME AS character_set,
COLLATION_NAME AS collation,
CHARACTER_MAXIMUM_LENGTH AS max_len,
CHARACTER_OCTET_LENGTH AS octet_len
FROM
tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME = 'kakusu_nvc';
table:result
column_name type character_set collation max_len octet_len
kakusu_nvc nvarchar UNICODE Japanese_Bushu_Kakusu_100_CI_AS_SC 3 6 code:truncate_and_select.sql
SELECT kakusu_nvc, LEN(kakusu_nvc) AS length, DATALENGTH(kakusu_nvc) AS data_length,
CAST(kakusu_nvc AS varbinary(max)) AS bin FROM #T; table:result
kakusu_nvc length data_length bin
a 1 2 0x6100
愛 1 2 0x1B61
𩸽 1 4 0x67D83DDE
未使用
code:drop.sql
-- 一時テーブルなのでログアウトしたら消えるが明示的に消したいとき用途
DROP TABLE tempdb.#T;