122

I am working on a project where another developer created a table with column names like 'Business Name'. That is a space between two words. If I run a SELECT statement with 'Business Name' it says there is no column with name 'Business'.

How can I solve this problem?

Don't Panic
  • 41,125
  • 10
  • 61
  • 80
ehp
  • 2,495
  • 6
  • 29
  • 43
  • I don't have enough reputation to comment properly, so tagged on the end: The solution posted above in the comments: **\`annoying_table\`.\`Business Name\`** did not work for me in mySQL in a where statement. Dropping the back quotes on the table name did work: i.e. **annoying_table.\`Business Name`** – Tim0th1 Aug 17 '17 at 08:54

6 Answers6

201

Generally the first step is to not do that in the first place, but if this is already done, then you need to resort to properly quoting your column names:

SELECT `Business Name` FROM annoying_table

Usually these sorts of things are created by people who have used something like Microsoft Access and always use a GUI to do their thing.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • @tadman If I copy and paste your query it works but if i type single quote from keyboard it does not work. ie. SELECT 'Business Name' FROM annoying_table does not work – ehp Jan 07 '13 at 06:28
  • 20
    It's because it's not single quotes but rather *tick* symbols which you can usually find on your keyboard to the left from digit 1. – peterm Jan 07 '13 at 06:33
  • 6
    There's three kinds of quotes, single `'`, double `"`, and backwards `\``. In MySQL the first two are equivalent and can be used interchangeably. This is not always the case on other platforms, and Postgres in particular treats them differently. The backticks are used only for database or column name escaping. – tadman Jan 07 '13 at 06:46
  • brackets [] didn't work for me. No errors but just didn't displayed it. ` on the other hand worked great. – Jay Dec 28 '13 at 12:30
  • The equivalent of brackets in Sybase or SQL Server is backticks in MySQL. – tadman Jan 06 '14 at 15:49
  • What if you need to identify the table as well? IE: `annoying_table.Business Name` does not seem to work with or without ticks. – Robert Oct 21 '14 at 05:46
  • 1
    @Robert Then you would write it like this: \`annoying_table\`.\`Business Name\`. – Erik Čerpnjak Mar 18 '15 at 08:36
  • that doesn't work. lm using sql eplorer 4.00 from 2001... `` also [] won't work – vincent thorpe Oct 29 '18 at 22:56
  • SQL Explorer is a front-end client. SQL Server or MySQL are the server software and dialect. – tadman Oct 29 '18 at 23:39
  • "... the first step is to not do that in the first place... ". But what about if I want `Business Name` to appear in reports as it is? i.e. a column name formed by two distinct words. – Gathide Mar 07 '20 at 18:55
  • 2
    @Gathide Do that in the presentation/application layer, not in the database. The names in the database should be concise, short yet meaningful, and side-step any issues with keyword conflict to avoid having to escape them. You can put *whatever you want, in whatever language the user wants* in the report. – tadman Mar 07 '20 at 19:05
27

If double quotes does not work , try including the string within square brackets.

For eg:

SELECT "Business Name","Other Name" FROM your_Table

can be changed as

SELECT [Business Name],[Other Name] FROM your_Table

FatherMathew
  • 960
  • 12
  • 15
25

You need to use backtick instead of single quotes:

Single quote - 'Business Name' - Wrong

Backtick - `Business Name` - Correct

Lawrence Cherone
  • 46,049
  • 7
  • 62
  • 106
Saurabh
  • 559
  • 6
  • 10
4

I got here with an MS Access problem.

Backticks are good for MySQL, but they create weird errors, like "Invalid Query Name: Query1" in MS Access, for MS Access only, use square brackets:

It should look like this

SELECT Customer.[Customer ID], Customer.[Full Name] ...
kztd
  • 3,121
  • 1
  • 20
  • 18
3

To each his own but the right way to code this is to rename the columns inserting underscore so there are no gaps. This will ensure zero errors when coding. When printing the column names for public display you could search-and-replace to replace the underscore with a space.

Valmiki
  • 49
  • 1
-6

I think double quotes works too:

SELECT "Business Name","Other Name" FROM your_Table

But I only tested on SQL Server NOT mySQL in case someone work with MS SQL Server.

Vin.X
  • 4,759
  • 3
  • 28
  • 35
  • 6
    This would always select the string "Business Name", not the content of the column. – JonasB May 01 '15 at 07:12
  • @JonasB Please check again, I used double quote not single quote.... Single quote will result in the string, double quote wont. – Vin.X Apr 17 '19 at 06:51
  • The question was specifically about MySQL and on a MySQL server with standard settings, double quotes and single quotes are equivalent. See this answer for further information why it's just a bad idea to use double quotes in MySQL: https://stackoverflow.com/a/14123649/4172652 – JonasB Apr 18 '19 at 08:52