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 an upper case , 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)
:
<national character string literal> ::=
N <quote> [ <character representation>... ] <quote>
[ { <separator>... <quote> [ <character representation>... ] <quote> }... ]
:
A <national character string literal> is equivalent to a <character string
literal> with the "N" replaced by "<introducer><character set
specification>", where "<character set specification>" is an
implementation-defined <character set name>.
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.
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 chose 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 parametrized queries the N prefix is not needed on any backend.
Tests were performed with the following SQL Filters:
Move ("SalesPerson.Name Like 'фа%'") to sFilter
Move ("SalesPerson.Name Like N'фа%'") to sFilter
SQLFilters Do NOT work without N prefix
SQLFilters work with N prefix
SQLFilters work without N prefix
SQLFilters work with N prefix
SQLFilters work without N prefix
SQLFilters work with N prefix
SQLFilters work without N prefix
SQLFilters work with N prefix
SQLFilters work without N prefix
SQLFilters work with 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 'фа%' "
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.