<cfsilence>

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

Posted At : December 4, 2006 10:32 AM | Posted By : todd sharp
Related Categories: SQL

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!

Comments (3) |

Print |

Send |

Code |

del.icio.us |

Linking Blogs | 3290 Views

Comments

[Add Comment]

It also works for column names with spaces and reserverd words.

# Posted By Jorrit | 12/5/06 3:12 AM

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 | 12/5/06 9:57 AM

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 | 5/19/08 11:42 AM

[Add Comment]