Is N'some string here' ANSI SQL?

Question

We know that in order to qualify a string literal as Unicode, we need to prefix it with N in Transact-SQL. My question is if this is part any standard such as ANSI SQL. Will other database platforms understand this?

UPDATE:

My practical experiments with Mig# show that in order to work with Unicode literals:

All editions of SQL Server (T-SQL): prefix required
MySql: prefix optional
SQLite: prefix not supported
Oracle: prefix supported but might need some additional configuration (see: NCHAR String Literal Replacement and How to save unicode data to oracle?)
Teradata: Unicode not supported

rory.ap · Accepted Answer · 2015-07-07T14:25:02.823

According to the MSDN documentation for constants in T-SQL, the N' prefix is part of the "SQL-92" standard:

Unicode strings have a format similar to character strings but are preceded by an N identifier (N stands for National Language in the SQL-92 standard).

According to Wikipedia, "National Character" strings were added in that version of the standard. That article references what appears to be the actual standard document http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt wherein it says:

The <key word>s NATIONAL CHARACTER are used to specify a character string data type with a particular implementation-defined character repertoire. Special syntax (N'string') is provided for representing literals in that character repertoire.

So, yes, it seems that it is definitely part of the standard.

UPDATE:

As Damien_The_Unbeliever pointed out in the comments here, the above-linked standard document is actually a draft (and supposedly the actual standard document is not publicly available), but in my opinion, there seems to be sufficient evidence that, at least inasmuch as the national character N is concerned, that is the standard.

Yes it is; since it's SQL-92 standard that means it's ANSI-92 standard as well. I was trying to find that doc but couldn't — Rahul, Jul 07 '15 at 13:56
It should be noted that the document you've linked to is technically a *draft* of the standard, not the published standard itself. I make use of that document myself but the published standard isn't freely available on the web. (I'm thus always in two minds of whether to link to it or quote from it since it's not official) — Damien_The_Unbeliever, Jul 07 '15 at 14:08
@Damien_The_Unbeliever -- Well, then i think it's sufficient to make a note, as you suggested. I will add that to my answer. — rory.ap, Jul 07 '15 at 14:19

score 1 · Answer 2 · answered Jul 07 '15 at 13:59

Under section

4.2 Character Strings

Of the SQL92 spec (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt)

The s NATIONAL CHARACTER are used to specify a character string data type with a particular implementation-defined character repertoire. Special syntax (N'string') is provided for representing literals in that character repertoire.

So yes, it is standard ANSI according to the most widely used spec

Is N'some string here' ANSI SQL?

2 Answers2