Special characters (subscripts) not shown proprely

Question

I want to put some chemical data into columns in the table. But in existing table subscripts are shown as normal characters. Some of them are shown as a question marks. What Am I supposed to do to fix it?

When I type this code

 SELECT N'H' + NCHAR(0x2082) + N'O'

It shows "H₂O" properly

When I put it into existing table

 INSERT INTO tab (id, label)
 VALUES('100', N'H' + NCHAR(0x2082) + N'O')

It shows "H2O" not correctly

Answer 1

you need to use NVARCHAR / NCHAR column in your table instead of VARCHAR / CHAR to store unicode characters.

In the example below, @table1, stores the formula correctly using NVARCHAR datatype whereas @table2 stores the same value in a VARCHAR column

DECLARE @table1 TABLE
(
    ID int,
    formula NVARCHAR(5)
)
DECLARE @table2 TABLE
(
    ID int,
    formula VARCHAR(5)
)

insert into @table1
SELECT '100', N'H' + NCHAR(0x2082) + N'O'

insert into @table2
SELECT '100', N'H' + NCHAR(0x2082) + N'O'

SELECT * FROM @table1;
SELECT * FROM @table2;