Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: What does this ..... do ???

    hi all

    I've looked at a lot of TSQL statements, and whenever they have some kinda of varchar e.g 'master', they'll prepend a N infront of it, e.g N'master'.

    A more concrete example is given below,When I execute the 2 sets of store procedures below, they return same results, I guess that would mean the N is there to filter something in cases where it is needed.

    But does any one know wat exactly N does??

    DECLARE @device_directory varchar(600)
    SELECT @device_directory = SUBSTRING(phyname, 1, CHARINDEX(N'master.mdf', LOWER(phyname)) - 1)
    FROM master.dbo.sysdevices
    WHERE (name = N'master')
    PRINT (@device_directory)

    DECLARE @device_directory varchar(600)
    SELECT @device_directory = SUBSTRING(phyname, 1, CHARINDEX('master.mdf', LOWER(phyname)) - 1)
    FROM master.dbo.sysdevices
    WHERE (name = 'master')
    PRINT (@device_directory)



    Cheers

    J

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    You use the N when you have columns that are unicode (eg nvarchar). When you query a unicode column you put the N' in front of your text.
    Johan

  3. #3
    Join Date
    Aug 2003
    Posts
    111
    Originally posted by jora
    You use the N when you have columns that are unicode (eg nvarchar). When you query a unicode column you put the N' in front of your text.
    thank you

  4. #4
    Join Date
    Aug 2003
    Posts
    111

    Re: What does this ..... do ???

    One more question tho.

    I am the type that would like to know every detail. So johan pointed out that N' should be used before unicode columns.

    But what exactly does N' do?

  5. #5
    Join Date
    Aug 2003
    Posts
    111
    I think I have partially figured out the answer to my own question, I do however need someone to confirm this.

    I have been looking at this code below while keeping in mind what johan said, I think this is how N works.
    1. N'....' is used to enclose whatever ..... is, for example, special characters like the single quote ' can't be used like this ''' in TSQL, instead we use N'''
    2. N just provides a way of escaping special characters

    Experts please confirm this.

    EXECUTE (N'CREATE DATABASE Temp
    ON PRIMARY (NAME = N''Temp'', FILENAME = N''' + @device_directory + N'temp.mdf'')
    LOG ON (NAME = N''Temp_log'', FILENAME = N''' + @device_directory + N'temp.ldf'')')
    go


    Cheer

    J

  6. #6
    Join Date
    Aug 2003
    Posts
    111
    i wasn't quite right, this is the full explaination taken from books online



    Using Unicode Data
    The Unicode specification defines a single encoding scheme for most characters widely used in businesses around the world. All computers consistently translate the bit patterns in Unicode data into characters using the single Unicode specification. This ensures that the same bit pattern is always converted to the same character on all computers. Data can be freely transferred from one database or computer to another without concern that the receiving system will translate the bit patterns into characters incorrectly.

    One problem with data types that use 1 byte to encode each character is that the data type can only represent 256 different characters. This forces multiple encoding specifications (or code pages) for different alphabets such as European alphabets, which are relatively small. It is also impossible to handle systems such as the Japanese Kanji or Korean Hangul alphabets that have thousands of characters.

    Each Microsoft® SQL Server™ collation has a code page that defines what patterns of bits represent each character in char, varchar, and text values. Individual columns and character constants can be assigned a different code page. Client computers use the code page associated with the operating system locale to interpret character bit patterns. There are many different code pages. Some characters appear on some code pages, but not on others. Some characters are defined with one bit pattern on some code pages, and with a different bit pattern on other code pages. When you build international systems that must handle different languages, it becomes difficult to pick code pages for all the computers that meet the language requirements of multiple countries. It is also difficult to ensure that every computer performs the correct translations when interfacing with a system using a different code page.

    The Unicode specification addresses this problem by using 2 bytes to encode each character. There are enough different patterns (65,536) in 2 bytes for a single specification covering the most common business languages. Because all Unicode systems consistently use the same bit patterns to represent all characters, there is no problem with characters being converted incorrectly when moving from one system to another. You can minimize character conversion issues by using Unicode data types throughout your system.

    In Microsoft SQL Server, these data types support Unicode data:

    nchar


    nvarchar


    ntext


    Note The n prefix for these data types comes from the SQL-92 standard for National (Unicode) data types.

    Use of nchar, nvarchar, and ntext is the same as char, varchar, and text, respectively, except that:

    Unicode supports a wider range of characters.


    More space is needed to store Unicode characters.


    The maximum size of nchar and nvarchar columns is 4,000 characters, not 8,000 characters like char and varchar.


    Unicode constants are specified with a leading N: N'A Unicode string'.


    All Unicode data uses the same Unicode code page. Collations do not control the code page used for Unicode columns, only attributes such as comparison rules and case sensitivity.

  7. #7
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    1. N'....' is used to enclose whatever ..... is, for example, special characters like the single quote ' can't be used like this ''' in TSQL, instead we use N'''
    2. N just provides a way of escaping special characters

    Nope.
    N'...' just defines an nvarchar which is a unicode string (2 bytes per char). Otherwise no different from '...'.
    It is needed in some cases - e.g. for non-ascii characters or sp_executesql.

    the N''...'' is because you are already in a string and want to embed a ' rather than terminate the string

    'abc''de'
    gives the string abc'de


    The code you have given could probably exclude the N.

  8. #8
    Join Date
    Aug 2003
    Posts
    111
    Originally posted by nigelrivett
    1. N'....' is used to enclose whatever ..... is, for example, special characters like the single quote ' can't be used like this ''' in TSQL, instead we use N'''
    2. N just provides a way of escaping special characters

    Nope.
    N'...' just defines an nvarchar which is a unicode string (2 bytes per char). Otherwise no different from '...'.
    It is needed in some cases - e.g. for non-ascii characters or sp_executesql.

    the N''...'' is because you are already in a string and want to embed a ' rather than terminate the string

    'abc''de'
    gives the string abc'de


    The code you have given could probably exclude the N.
    Thanx for your correction.

    Cheers

    J

  9. #9
    Join Date
    Jul 2003
    Location
    Ohio/Chicago
    Posts
    75
    on a related note, i'm trying to us XP_sendmail with unicode characters, Should I be using N'XP_sendmail to do this because I can't seem to get it working and it always spews out ? marks. If I store the actual #55555; value in teh DB I can read it in the email, so I'm sure it has to do with something as to how i'm handling this SP.....any thoughts?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •