SQL Server: Storing language specific data in SQL Server table field.

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.

	USE MASTER
	GO
	-- Drop and Create TestMultiLingualDB
	IF db_id('TestMultiLingualDB') IS NOT NULL
	DROP DATABASE TestMultiLingualDB
	GO
	SET NOCOUNT ON
	GO
	CREATE DATABASE TestMultiLingualDB
	GO
	USE TestMultiLingualDB
	GO
	CREATE TABLE MultiLanguage (Id INT, Var_Field VARCHAR(50), NVar_Field NVARCHAR(50))
	GO
	-- Insert Hindi Characters
	INSERT INTO MultiLanguage VALUES (1, N'यह एक शब्द है', N'यह एक शब्द है')
	GO
	-- Insert Punjabi Characters
	INSERT INTO MultiLanguage VALUES (2, N'ਇਹ ਇੱਕ ਸ਼ਬਦ ਦਾ ਹੁੰਦਾ ਹੈ', N'ਇਹ ਇੱਕ ਸ਼ਬਦ ਦਾ ਹੁੰਦਾ ਹੈ')
	GO
	--Select and see inserted values.
	SELECT * FROM MultiLanguage
	GO
	-- Drop TestMultiLingualDB
	USE MASTER
	GO
	DROP DATABASE TestMultiLingualDB
	GO

view raw multilingual-scripts.sql hosted with ❤ by GitHub

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.

Comments

[Aaron Reese]

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.