locked
unicode string RRS feed

  • Question

  • When I execute dynamic sql:

    DECLARE @sql NVARCHAR(100)	
    SET @sql=N'select col1 from dbo.myTable'
    
    EXEC SP_EXECUTESQL @sql

    You should declare string text as with N''. But if you skip N, it works totally the same:
    SET @sql='select col1 from dbo.myTable'
    So, is it needed to write "N" when create unicode string or it is not? I can't see any difference in execution.
    I guess not since declaration of @sql variable already tell us it is unicode.

    br, Simon
    Friday, April 17, 2015 8:55 AM

Answers

  • Although sometimes called "extended ASCII characters", code points 128-255 aren't really part of the ASCII character set.  These are mapped to characters according to code pages.  In SQL Server, the code page used for non-Unicode data are determined by the database collation.

    Note that sp_executeSQL takes an nvarchar(MAX) parameter so any supplied parameter is implicitly converted to Unicode.  But non-Unicode literal values are first interpreted according to the current database collation.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by simonxy Friday, April 17, 2015 12:54 PM
    Friday, April 17, 2015 12:48 PM

All replies

  • The parameters for sp_executesql are unicode by definition ie nvarchar. So it can accept unicode. But relevance of N'' comes only when you want to store characters as unicode ie other language characters. WHile passing query in english even if you skip it will work fine

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, April 17, 2015 8:57 AM
  • So, if I understand correctly, can you check this example:

    I have productName column, which is varchar(100) and has slovenian collation, which has characters like Č,Š,Ž which are not part of english.

    Now i have dynamic query:

    DECLARE @sql nVarChar(200)
    
    SET @sql='SELECT productID FROM dbo.products WHERE productName LIKE ''ŠČŽ%'''
    
    EXEC SP_EXECUTESQL @sql

    This query should not return correct results since unicode @sql variable stores characters other than english and there is no N'' in front.

    But when I have tried it works the same, with N'' or without.

    Still, there is no need to write N in front of string variable when we don't have english characters?

    Friday, April 17, 2015 9:17 AM
  • You will see the difference when you PRINT your statement as Unicode with N and as ASCII without N:

    PRINT N'SELECT productID FROM dbo.products WHERE productName LIKE ''ŠČŽ%'''
    PRINT  'SELECT productID FROM dbo.products WHERE productName LIKE ''ŠČŽ%'''

    Result.

    SELECT productID FROM dbo.products WHERE productName LIKE 'ŠČŽ%'
    SELECT productID FROM dbo.products WHERE productName LIKE 'ŠCŽ%'

    You see, Unicode sign Č changed to ASCII C; so there is a difference.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by cnk_gr Friday, April 17, 2015 11:18 AM
    Friday, April 17, 2015 11:15 AM
  • Well,

    I see the same in both cases.
    I have connection language as English.
    Is there some setting in SSMS that I can see all Slovenian characters correctly and you don't. Or it is from windows?

    Otherwise I would stick with N''.

    Thanks,
    Simon

    Friday, April 17, 2015 11:26 AM
  • Hello Simon,

    It can depend on the used server/database collation, may be your server do have collation Slovenian_100_CI_AI. You can query it with

    SELECT name, collation_name
    FROM sys.databases

    My Server/databases do have collation "Latin1_General_CI_AS". See Collation (Transact-SQL) and Collation and Unicode Support


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, April 17, 2015 11:43 AM
  • Thanks, it is true, database collation is the one.
    Just one more. Why do you see Ž and Š? None of this characters are included in Extended ASCII characters:
    http://www.theasciicode.com.ar/

    Friday, April 17, 2015 11:56 AM
  • It is simply because my Windows regional settings and that I have a different codepage (1252 for west-Europe) then you have (may be 1257 – Baltic). See http://en.wikipedia.org/wiki/Code_page

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, April 17, 2015 12:31 PM
  • Although sometimes called "extended ASCII characters", code points 128-255 aren't really part of the ASCII character set.  These are mapped to characters according to code pages.  In SQL Server, the code page used for non-Unicode data are determined by the database collation.

    Note that sp_executeSQL takes an nvarchar(MAX) parameter so any supplied parameter is implicitly converted to Unicode.  But non-Unicode literal values are first interpreted according to the current database collation.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by simonxy Friday, April 17, 2015 12:54 PM
    Friday, April 17, 2015 12:48 PM
  • Try this:

    DECLARE @x nvarchar(123) = N'räksmörgås',
            @y nvarchar(123) = 'räksmörgås'

    They should not display the same for you; the å should be replaced with "a", because "å" is not available in code page 1250, the code page for Slovenian collations.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, April 17, 2015 10:10 PM
  • Erland,

    just one more.

    å has unicode number: U+00E5

    But if you are using ASCII(non unicode string) and you have Windows-1252 character encoding, then it has position 229 in character table:http://en.wikipedia.org/wiki/Windows-1252(by the way, is this windows related or it depends on collation of database engine server configuration).

    I have slovenian collation setting (also slovenian language in windows), so, I have Windows-1250 character encoding: http://en.wikipedia.org/wiki/Windows-1250
    Here on 229 letter position is "ĺ" and "a" has 97 character position, while "å" is not in my character table as you already mentioned. Why I see "a" instead of "å". How it is related? Why I don't see "ĺ" for example instead of "å"?
    br, Simon


    • Edited by simonxy Tuesday, April 21, 2015 8:44 AM correction
    Tuesday, April 21, 2015 8:41 AM
  • Because no matter you have '' or N'', SQL Server sees a Unicode string, because that is how SSMS sends the command to SQL Server. So SQL Server sees a Unicode byte pattern, which it needs to convert to varchar using the Slovenian code page. It finds that "å" is not available in that code page, and rather than using ?, which is the generic fallback character, it takes a character that looks similar. (More exactly, the scheme is probably devised by an English-speaking person who thinks that ring is just some funny garnishment on the "a". Nevermind that "å" is a letter of its own in the Swedish alphabet.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, April 21, 2015 12:29 PM