Decode and Encode Unicode data in SQL Server
While working on a internet and intranet website with detailed description of cities around the world, I faced difficulties in the encoding and decoding of Unicode data.
SQL Server can store Unicode data with the nvarchar data type, but encoding and decoding a string into real Unicode is a bit less straightforward than it may seem.
A Unicode character like ķ, a.k.a latin small K with cedilla, can be represented as ķ in other words a prefix (&#), a 3 to 5 digit code and a suffix (;).
There are 65,535 Unicode character in total.
Below two utility queries to encode and decode Unicode in SQL Server.