Insert multi language data in table

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..

NVARCHAR

@@Language Function

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 287 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.