确定 nvarchar 列中的 varchar 内容 [英] Determine varchar content in nvarchar columns

问题描述

我有一堆 NVARCHAR 列，我怀疑这些列在 VARCHAR 列中包含完全可存储的数据.但是，我不能只是将列的类型更改为 VARCHAR 并希望最好，我需要进行某种检查.

我想进行转换，因为数据是静态的(将来不会改变)并且列被索引并且与实际的 (nvarchar) 索引相比会受益于较小的 (varchar) 索引.

如果我只是说

ALTER TABLE TableName ALTER COLUMN columnName VARCHAR(200)

那么我就不会收到错误或警告.Unicode 数据将被截断/丢失.

我如何检查?

解决方案

为什么不投射到那里然后返回看看丢失了哪些数据?

这假设列是 nvarchar(200) 开头

SELECT *从表名WHERE 列名 <>CAST(CAST(columnName AS varchar(200)) AS nvarchar(200))

I have a bunch of NVARCHAR columns which I suspect contain perfectly storable data in VARCHAR columns. However I can't just go and change the columns' type into VARCHAR and hope for the best, I need to do some sort of check.

I want to do the conversion because the data is static (it won't change in the future) and the columns are indexed and would benefit from a smaller (varchar) index compared to the actual (nvarchar) index.

If I simply say

ALTER TABLE TableName ALTER COLUMN columnName VARCHAR(200)

then I won't get an error or a warning. Unicode data will be truncated/lost.

How do I check?

解决方案

Why not cast there and back to see what data gets lost?

This assumes column is nvarchar(200) to start with

SELECT *
FROM TableName
WHERE columnName <> CAST(CAST(columnName AS varchar(200)) AS nvarchar(200))

这篇关于确定 nvarchar 列中的 varchar 内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！