While working on multi-language support we need to insert different language data in the same field or different fields of a SQL Server table.
There are two basic rules one needs to keep in mind while storing multi-lingual or Unicode data:
- First, the column must be of Unicode data type (i.e.,
nchar,nvarchar,ntext). - And second is that the value must be prefixed with N while insertion.
Below is the sample script with output to understand it better.
In the output, you can see that values in Var_Field are saved as ????, even though in script N is prefixed while inserting. This is because the field is of VARCHAR type.
| Id | Var_Field | NVar_Field |
|---|---|---|
| 1 | ?? ?? ???? ?? | यह एक शब्द है |
| 1 | ?? ??? ???? ?? ????? ?? | ਇਹ ਇੱਕ ਸ਼ਬਦ ਦਾ ਹੁੰਦਾ ਹੈ |
Note:
If you are using a stored procedure to insert the Unicode data, then make sure that N is used before the string while passing the parameter to the stored procedure. Also, make sure the parameter declared in the stored procedure is of Unicode type.
Originally published at http://diary-techies.blogspot.com on
July 19, 2016.
Top comments (1)
Just be aware that Unicode strings will preserve whitespace at the end of your strings. This can lead to some unexpected behaviour when using LIKE predicate matches. There are options to disable this behaviour.