The N Prefix in SQL

Background

The "N" prefix stands for National Language in the SQL-92 standard and is used for representing Unicode characters. In the current standard, it must be in uppercase, which is what you will typically find implemented in mainstream products.

Support for additional data types (DATE, TIME, TIMESTAMP,
INTERVAL, BIT string, variable-length character and bit strings,
and NATIONAL CHARACTER strings)
:
::=
N
[
... ]
[ {
...
[
... ]
}... ]
:
A
is equivalent to a
literal> with the "N" replaced by "
specification>", where "
" is an
implementation-defined
.

While most databases do not need the added N prefix when using Unicode data, in SQL Server, you must precede all Unicode strings with a prefix N when dealing with Unicode string constants. If you do not prefix a Unicode string constant with N, SQL Server will convert it to the non-Unicode code page of the current database before it uses the string.

The "N" Prefix in DataFlex 2021

From our tests (see below), the only database that indicated the N prefix was required was SQL Server. All other supported backends work with or without the N prefix; the N prefix is not required in those environments but does work if used.

In DataFlex 2021, the SQLStrLike function in cDataDictionary will always add the N prefix, which allows that function to work properly when used against any backend.

When using Embedded SQL, developers can either choose to apply the N prefix as required by the backend or always use it (like we chose to do for SQLStrLike). For SQL Server, for example, the N prefix is required, while for other backends, it is not.

For parameterized queries, the N prefix is not needed on any backend.

SQL Filters and N Prefix

Tests were performed with the following SQL Filters:

Without N prefix

Move ("SalesPerson.Name Like 'фа%'") to sFilter

With N prefix

Move ("SalesPerson.Name Like N'фа%'") to sFilter

Results

SQL Server
- SQL Filters do NOT work without N prefix
- SQL Filters work with N prefix
MySQL
- SQL Filters work without N prefix
- SQL Filters work with N prefix
PostgreSQL
- SQL Filters work without N prefix
- SQL Filters work with N prefix
DB2 vargraphic
- SQL Filters work without N prefix
- SQL Filters work with N prefix
DB2 varchar
- SQL Filters work without N prefix
- SQL Filters work with N prefix

Embedded SQL and N Prefix

Both of the statements below will work on all backends tested except SQL Server. On SQL Server, you must use the N version.

Send SQLExecDirect of hoStmt "Select ID, Name from SalesPerson Where SalesPerson.Name Like N'фа%'"
Send SQLExecDirect of hoStmt "Select ID, Name from SalesPerson Where SalesPerson.Name Like 'фа%'"

Parameterized Queries and N Prefix

To test parameterized queries, the following program was used:

Move "Select Order_Number, Customer_Number, SalesPerson_ID, Order_Date from OrderHeader Where SalesPerson_ID = ? And Order_Date > ?" to sSQLQuery
Move 1 to iParamNum
Move 'фаль' to sSalesPersonID
Send SqlSetParameter of hstmt iParamNum sSalesPersonID typeString
Move 2 to iParamNum
Move (DateSet(2015, 12, 25)) to dOrderDate
Send SqlSetParameter of hstmt iParamNum dOrderDate typeDate
Send SQLExecute of hstmt
Get SQLFetchResultSetValues of hstmt to sResultSet

The SalesPerson.ID used in this program is an NVarchar column. The query returned the expected result, i.e., the correct Unicode data, without the need to use the N prefix.