Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

The title basically says it: Are there any major DBs that don't handle column name quoting/escaping?

For instance, suppose I want to have a column called FROM (and let's assume I have a really good reason for that). In every SQL-based RDBMS, naturally that's a keyword. In MS SQL Server I can escape it using [ and ]:

CREATE TABLE FOO (
    [FROM] VARCHAR(255)
)

MySQL uses backticks

CREATE TABLE FOO (
    `FROM` VARCHAR(255)
)

Similarly, Oracle and Postegres use double quotes.

Is there any major RDBMS that doesn't support quoting/escaping column names, one way or another?

share|improve this question
1  
Because column names are variable, and can be allowed to have spaces (they're descriptors, not hard-variables, and are for your benefit), then all column names must be escapable. – jcolebrand♦ Dec 4 '12 at 20:35
5  
I doubt it <delimited identifier> is in the SQL 92 spec – Martin Smith Dec 4 '12 at 20:57
2  
"Similarly, Oracle and Postegres use double quotes". That is actually defined by the SQL standard and pretty much every other database does so. SQL Server as well as MySQL also allow the standard way of quoting identifiers (which I personally prefer over the DBMS specific non-standard way). – a_horse_with_no_name Dec 4 '12 at 21:15
1  
Which RDBMS do you consider to be major? – Leigh Riffel Dec 4 '12 at 21:18
1  
@MartinSmith: Presuming, of course, that engines follow the spec. Which seems quite a large presumption. – T.J. Crowder Dec 4 '12 at 22:31
show 6 more comments

migrated from stackoverflow.com Dec 4 '12 at 20:32

1 Answer

up vote 5 down vote

As Leigh Riffel said, the answer to my question depends partially on what you mean by "major."

I'm going with: No, there are no major RDBMS engines out there that can't handle name escaping/quoting. A quoted/escaped name is apparently properly called a delimited identifier.

Martin Smith pointed out that delimited identifier is in the SQL-92 specification:

<delimited identifier> ::=
    <double quote> <delimited identifier body> <double quote>

Now, how well specifications are followed varies widely, but...

I've verified the following engines:

  • Microsoft SQL Server supports the standard "name" at least as far back as SQL Server 2000 (and also its own non-standard [name])
  • Oracle supports the standard
  • MySQL uses backticks (`name`) by default, but if you set SET GLOBAL SQL_MODE=ANSI_QUOTES; it supports the standard
  • Postgres supports the standard
  • SQLite (I said it depended on what you call "major") supports the standard and SQL Server's [name]

I figure the first four up there cover the vast majority of installations, and the fifth is important for embedded database work (as are a couple from the following list).

a_horse_with_no_name tells us that all of these support it too:

  • Sybase (he says Sybase does it like Microsoft)
  • DB2
  • Apache Derby
  • H2
  • HSQLDB

(This being a Stack Exchange site, if you know of others that also support this, feel free to edit the answer and add them directly if you have enough rep. If you don't, just comment and I'll fold them in.)

share|improve this answer
2  
DB2, Apache Derby, H2, HSQLDB, and Sybase can be added to that list. All of them support standard quoting, Sybase does it like Microsoft. – a_horse_with_no_name Dec 6 '12 at 12:13
@a_horse_with_no_name: Thanks, added! If you know of others as well, feel free to edit directly. :-) – T.J. Crowder Dec 6 '12 at 12:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.