Inserting special characters (greater/less than or equal symbol) into SQL Server database

Question

I am trying to insert ≤ and ≥ into a symbol table where the column is of type nvarchar.

Is this possible or are these symbols not allowed in SQL Server?

Add the `N` prefix if this is not a standard character in your default collation. — Martin Smith, Aug 19 '12 at 17:51

score 22 · Accepted Answer · edited Dec 19 '18 at 14:02

22

To make it work, prefix the string with N

create table symboltable 
(
  val nvarchar(10)
)

insert into symboltable values(N'≥') 

select *
from symboltable

enter image description here

Further Reading:

edited Dec 19 '18 at 14:02

劉鎮瑲

517
9
20

answered Aug 19 '12 at 17:50

Gonzalo.-

12,512
5
50
82

1

anyway, without your downvote I wouldn't get the correct answer. That makes me do some research. Thanks ! – Gonzalo.- Aug 19 '12 at 17:57
2

Thanks. Completely forgot about the N prefix. – user1610304 Aug 19 '12 at 18:14
How it would work for me? http://stackoverflow.com/questions/32253235/how-to-use-if-else-statement-to-update-or-create-new-xml-node-entry-in-sql Thanks. – Si8 Aug 27 '15 at 15:27

score 4 · Answer 2 · answered Aug 15 '18 at 00:35

To add to gonzalo's answer, both the string literal and the field need to support unicode characters.

String Literal

Per Marc Gravell's answer on What does N' stands for in a SQL script ?:

'abcd' is a literal for a [var]char string, occupying 4 bytes memory, and using whatever code-page the SQL server is configured for.
N'abcd' is a literal for a n[var]char string, occupying 8 bytes of memory, and using UTF-16.

Where the N prefix stands for "National" Language in the SQL-92 standard and is used for representing unicode characters. For example, in the following code, any unicode characters in the basic string literal are first encoded into SQL Server's "code page":

Aside: You can check your code page with the following SQL:
SELECT DATABASEPROPERTYEX('dbName', 'Collation') AS dbCollation;
SELECT COLLATIONPROPERTY( 'SQL_Latin1_General_CP1_CI_AS' , 'CodePage' ) AS [CodePage];
The default is Windows-1252 which only contains these 256 characters

Field Type

Once the values are capable of being passed, they'll also need to be capable of being stored into a column that supports unicode types, for example:

nchar
nvarchar
ntext

Further Reading:

Inserting special characters (greater/less than or equal symbol) into SQL Server database

2 Answers2

String Literal

Field Type

Linked