varcharの列にUnicode文字をINSERTしてみる
環境
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)
);
varcharの確認
カラムの情報
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 = 'vc';
table:result
column_name type character_set collation max_len octet_len
vc varchar cp932 Japanese_CI_AS 3 3 文字をINSERTしてみる
code:truncate_and_select.sql
INSERT INTO #T(vc) VALUES ('a'); INSERT INTO #T(vc) VALUES ('愛'); INSERT INTO #T(vc) VALUES ('𩸽'); SELECT vc, LEN(vc) AS length, DATALENGTH(vc) AS data_length,
CAST(vc AS varbinary(max)) AS bin FROM #T; table:result
vc length data_length bin
a 1 1 0x61
愛 1 2 0x88A4
?? 2 2 0x3F3F
未使用
code:drop.sql
-- 一時テーブルなのでログアウトしたら消えるが明示的に消したいとき用途
DROP TABLE tempdb.#T;