Post reply

" in T-SQL

  • December 12, 2008 at 6:50 am

    #125753

    Hi, I have been looking at a legacy system that I have responsibility for and have noticed a lot of use of " (double quotation rather than two single quotes) ie:

    SELECT

    "col_1",

    "col_2"

    FROM

    "dbo"."Table"

    I have never seen this before and was curious as to whether it is old syntax?

    Thanks

  • December 12, 2008 at 7:08 am

    #911610

    while sql server defaults to using brackets for delimiting object names, like

    [Accounting Table].dbo.[Field Name],

    other systems, like Oracle, use double quotes; i think that is an ANSI requirement...which is why it works fine in SQL if you call

    "Accounting Table".dbo."Field Name"

    code with dbl quotes should, in theory, be copy/pastable to use in other DBMS systems.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 12, 2008 at 7:18 am

    #911621

    I think I saw something about that being deprecated out of SQL in an upcoming release. Can't find the link at the moment though.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • December 12, 2008 at 7:37 am

    #911642

    Thanks for the reponses guys.

    I think I saw something about that being deprecated out of SQL in an upcoming release. Can't find the link at the moment though.

    I thought I had heard something about it being a depreciated syntax in an upcoming version, but like you I could not find anything.

    code with dbl quotes should, in theory, be copy/pastable to use in other DBMS systems.

    All of the systems originally stemmed from an access db so it may be from there, but I had not seen that in access either.

    Thanks for the clarification.

    billy

  • December 12, 2008 at 12:04 pm

    #911856

    Also the ability to wrap your column names with the " depends on your Quoted_Identifier setting.

    From Books Online

    http://msdn.microsoft.com/en-us/library/ms174393(SQL.90).aspx

    SET QUOTED_IDENTIFIER (Transact-SQL)

    Causes SQL Server 2005 to follow the SQL-92 rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers

    Examples

    A. Using the quoted identifier setting and reserved word object names

    The following example shows that the SET QUOTED_IDENTIFIER setting must be ON, and the keywords in table names must be in double quotation marks to create and use objects that have reserved keyword names.

    Copy Code

    SET QUOTED_IDENTIFIER OFF

    GO

    -- An attempt to create a table with a reserved keyword as a name

    -- should fail.

    CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL)

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- Will succeed.

    CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL)

    GO

    SELECT "identity","order"

    FROM "select"

    ORDER BY "order"

    GO

    DROP TABLE "SELECT"

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply