Insert subscript value in sql server varchar column

Question

I am having a table with 2 columns.

---

id: number, name : varchar(50)

---

I want to insert oxygen (O2) value in name field, 2 as subscript. when i am inserting, it is not able to save it as subscript value.

one solution is to change name's data type to nvarchar. but i am looking for better solution, So that i do not need to change in table.

Answer 1

The best solution is to change the column into nvarchar(50).

If you stick to varchar(50), the underlying set of representable characters depends on your collation. In general by collation we mean the sort order, e.g. does Ø sort together with O or as a separate letter after Z, and is it earlier or later than Å. But the collation also correlates to the underlying character set (set of representable characters). Try the query:

select TABLE_CATALOG, TABLE_NAME, COLUMN_NAME,
    DATA_TYPE, CHARACTER_SET_NAME,
    COLLATION_NAME, COLLATIONPROPERTY(COLLATION_NAME, 'CodePage') as CODE_PAGE
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = N'name'

and note the CHARACTER_SET_NAME, COLLATION_NAME, and CODE_PAGE for your column. What do you have?

I highly doubt it will be something which can represent 'O₂' correctly.

---

You could choose some convention, for example using HTML-like constructs such as 'O<sub>2</sub>', or TeX-like ones such as 'O_2'.

Answer 2

I think only ASCII and Extended ASCII are allowed in char/varchar field. But if you use nchar/nvarchar then you can use any characters.