Sometimes, you need to store multi language or multilingual data in any table column. Inserting a multilingual data into column without following some basics rules, you see that when you select data from table, it returns ????, instead of multilingual data that you had inserted.
There are some rules that you need to keep in mind while dealing with multi language data as follows.
- Column that you are going to use to store multilingual data, must be of unicode data type such as NVARCHAR, NCHAR, NTEXT.
- When a multilingual data is inserted into column from table, column value should be prefixed with N’.
To demonstrate this , lets create a table and insert some multilingual data into this.
CREATE TABLE dbo.tbl_TestMultilingualData ( ID INT IDENTITY(1,1), Lang_1 VARCHAR(500), Lang_2 NVARCHAR(500) )
Lets insert some records into table as shown below.
INSERT INTO
dbo.tbl_TestMultilingualData
(Lang_1, Lang_2)
VALUES
('हिंदी', N'हिंदी'),
('日本人', N'日本人'),
('سنڌي', N'سنڌي'),
('عربى', N'عربى')
As you can see, data have been inserted into table.
You will notice that, data in second column Lang_2 is inserted with prefix N, also data type of this column is NVARCHAR, which is the some basic rules of inserting multilingual data.
Lets fetch the records from table.
SELECT * FROM dbo.tbl_TestMultilingualData
You can see, For first column Lang_1 which has data type VARCHAR, does not support the multilingual data that why it returns ????.
While second column Lang_2 has data type NVARCHAR and data is inserted with prefix N, and you can see multilingual that you had inserted in column.
Also note that if you have a column of unicode data type NVARCHAR, NCHAR, NTEXT but you have not used prefix N while inserting the data into column, it will return ???.
So, It is must to storing a multilingual data into table, that data type of column must be of unicode data type and value is prefixed with N while inserting a data into column.
Lets truncate the table dbo.tbl_TestMultilingualData first, then again insert some records without prefix N.
TRUNCATE TABLE dbo.tbl_TestMultilingualData
Lets insert records into table as shown below.
INSERT INTO
dbo.tbl_TestMultilingualData
(Lang_1, Lang_2)
VALUES
('हिंदी', 'हिंदी'),
(N'日本人', N'日本人'),
('سنڌي', 'سنڌي'),
(N'عربى', N'عربى')
You can see, data for first and third row is inserted without Prefix N.
Lets see the records in table.
SELECT * FROM dbo.tbl_TestMultilingualData
You can see, the values in second column Lang_2, even though the data type of this column is NVARCHAR but value in first and third row for this column does not support multilingual data and returns ???., the reason is that prefix N is not used while inserting a values for them.
Also Read..
