Determine Varchar Content in Nvarchar Columns

Determine varchar content in nvarchar columns

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))

How can I tell if a column can be made into a varChar from a nVarChar?

The other current answer is missing a step. All it checks is that after converting to varchar they still compare equal - not that they are actually the same character.

The string N'⁴＂＊８？' would silently be converted to '4"*8?' and still compare equal under many collations.

See

SELECT nvarcharcolumn,
       CAST(nvarcharcolumn AS VARCHAR(4000))
FROM   ( VALUES(N'⁴＂＊８？')) V(nvarcharcolumn )
WHERE  nvarcharcolumn <> cast(nvarcharcolumn AS VARCHAR(4000)) 

I would check that it round trips to the exact same value after casting to varchar and back again.You can do this with

WHERE nvarcharcolumn <> cast(nvarcharcolumn as varchar(4000)) collate Latin1_General_100_BIN 

Get nvarchar records that were inserted as varchar

You can't, because it was converted to ? on insertion into the table. The original data is gone. A literal string is created as a VARCHAR unless you prefix it with N.

Find all columns of type nvarchar(max) in all tables in a SQL Server database

The character_maximum_length will be -1 for max.

select 
    table_name as [Table Name]
  , column_name as [Column Name]
from information_schema.columns 
where data_type = 'nvarchar' 
  and character_maximum_length=-1

Find out if column has varchar 20

The COLUMNS information schema view is ideal for this.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = [table] AND COLUMN_NAME = [column]
AND DATA_TYPE = 'varchar' AND CHARACTER_MAXIMUM_LENGTH = 20

Column declared as NVARCHAR gets created as VARCHAR in MySQL. Both VARCHAR AND NVARCHAR declaration can store non latin characters

Which information you can save is stored in character set and collation.

so as the default is utf8, bith can save hindi or chines or kisuali in their 4 byites

but

CREATE TABLE table1 ( column1 NVARCHAR(10),column2 VARCHAR(10) );

Actually is treated slightly different

CREATE TABLE `table1` (
  `column1` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `column2` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

in the sample database the Default is

DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

But the national varchar is like the standard defines

CHARACTER SET utf8 COLLATE utf8_general_ci

For your hindi word "भारत" it makes no differenz, but for some charachters there can be "problems"

How do you view ALL text from an ntext or nvarchar(max) in SSMS?

In newer versions of SSMS it can be configured in the (Query/Query Options/Results/Grid/Maximum Characters Retrieved) menu:

---

Old versions of SSMS

Options (Query Results/SQL Server/Results to Grid Page)

To change the options for the current queries, click Query Options on the Query menu, or right-click in the SQL Server Query window and select Query Options.

...

Maximum Characters Retrieved

Enter a number from 1 through 65535 to specify the maximum number of characters that will be displayed in each cell.

Maximum is, as you see, 64k. The default is much smaller.

BTW Results to Text has even more drastic limitation:

Maximum number of characters displayed in each column

This value defaults to 256. Increase this value to display larger result sets without truncation. The maximum value is 8,192.

---

Related Topics