How to select a column name with a space in MySQL
MysqlSqlMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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
Solution 3 - Mysql
You need to use backtick instead of single quotes:
Single quote - 'Business Name'
- Wrong
Backtick - `Business Name`
- Correct
Solution 4 - Mysql
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.
Solution 5 - Mysql
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] ...
Solution 6 - Mysql
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.