Working With Bad SQL Column Names (Even If It's Your Fault)

Posted By : todd sharp Posted At : December 4, 2006 9:32 AM Posted In: SQL

7

It must be SQL day at cfsilence. This is really obvious stuff, but even supreme coders like myself make mistakes now and then (and if you really think I believe that I am a supreme coder, I have some land in Florida I'd like to sell you). I just pulled an Excel file into my SQL Server 2000 database at work to use as a temp table to update an existing table with a new 'rank' for 2007. Well, it just so happened that the rank column was named '2007_mw_rank' - not the best column name, but it was there so I went with it since it was only a temp table. Apparently column names in SQL Server can not begin with a number (as I said, I normally wouldn't have done it). So when I went to query the table with the following query:

select *
FROM temp_2007_priority
WHERE 2007_mw_rank IS NULL

I received the following error:

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '_mw_rank'.

Like all poorly named columns, surrounding the column name with [] was a temp fix:

select *
FROM temp_2007_priority
WHERE [2007_mw_rank] IS NULL

But the best solution would have been to rename the column so that it did not begin with a number at all!

8305 Views Send Print Comments (7)

Comments (7)

Subscribe to Comments Add Comment

Jorrit's Gravatar It also works for column names with spaces and reserverd words.

Posted by: Jorrit on: 12/5/06 3:12 AM

Steve Bryant's Gravatar Note that you can escape column names in other databases, but the syntax varies from database to database.

Access uses brackets like SQL Server: [my column name]
MySQL uses ticks: `my column name`
Oracle uses quotes: "my column name"
PostGreSQL uses quotes: "my column name"

Posted by: Steve Bryant on: 12/5/06 9:57 AM

hypotheekrente's Gravatar Thanks for your information, i was working with a qeury with numbers as table names, but got wrong results so this helps alot.. and i will need to rename then :)

Regards, Aislin

Posted by: hypotheekrente on: 5/19/08 11:42 AM

andy's Gravatar Lol, thank you, I just spent an hour debugging a SQL call because one of the field names was 'Delete'. Doh!

Posted by: andy on: 12/16/08 5:28 PM

Hypotheek's Gravatar Thanks for posting this information!
Regards,
Angela

Posted by: Hypotheek on: 2/5/09 9:33 AM

DonOmite's Gravatar Interesting. But I ran into the problem of column names with spaces in them and none of these solutions worked. Of course then I slapped myself in the head and said "IT IS AN ACCESS MDB!" So I just changed the names. :)
A customer sent me an MDB that he wanted loaded into MSSQL. Seemed simple enough until my direct link broke so I had to program it. Still seemed to be a piece of cake. Just select all the data from the access db and insert it into the MSSQL db. But when I tried
INSERT INTO newtable
(COL1,COL2)
VALUES
('[stupid name 1]','[stupid name 2]')

It did not work.
After pounding my head against the monitor I realized he didn't need the mdb returned and I had total control of it. Stoopid me. DOH *slap in the head*

But I may play around and figure out why it isn't working.

Posted by: DonOmite on: 11/5/09 12:49 PM

Steve Bryant's Gravatar DonOmite,

The escapes are for the table and field names, not the values.

So, you would want:

INSERT INTO newtable
([COL1],[COL2])
VALUES
('stupid name 1','stupid name 2')

If you are using SELECT INTO, then you don't want a VALUES clause in your insert statement.

Posted by: Steve Bryant on: 11/5/09 2:04 PM
Add Comment

  • Calendar

    << February 2010 >>
    Sun Mon Tue Wed Thu Fri Sat
     123456
    7 8 9 10 11 12 13
    14 15 16 17 18 19 20
    21 22 23 24 25 26 27
    28       

  • Subscribe

    • Enter your email address to subscribe to this blog.