Casting Strings to Wrong Code Pages in SQL Server Transact-SQL

Q

What Happens If Strings Are Casted into Wrong Code Pages in SQL Server Transact-SQL?

✍: FYIcenter.com

A

In SQL Server, different collations may use different code pages. For example:

  • Albanian_CI_AI_KS_WS - Albanian, Code page 1250.
  • Arabic_CI_AS_KS_WS - Arabic, Code page 1256.
  • French_CI_AI - French, Code page 1252.
  • Korean_Wansung_BIN - Korean-Wansung, Code page 949.
  • SQL_Latin1_General_CP1250_CI_AS - Latin1-General, Code page 1250.

If you are casting a string of characters from one code page to a different code page, some characters will be converted to similar characters. For example

PRINT 'Français: eéèê-aà-oô'; 
  -- The default code page

PRINT 'Français: eéèê-aà-oô' 
  COLLATE French_CI_AI; -- Code page 1252

PRINT 'Français: eéèê-aà-oô' 
  COLLATE Polish_CI_AS; -- Code page 1250

PRINT 'Français: eéèê-aà-oô' 
   COLLATE Cyrillic_General_CI_AS; -- Code page 1256

Français: eéèê-aà-oô
Français: eéèê-aà-oô
Français: eéee-aa-oô
Francais: eeee-aa-oo

 

Data Literals in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2017-05-13, 236👍, 0💬