Alias For Column with Spaces?

Question

I need a query that includes 3 tables and 2 inner joins. One of the column names in my SELECT statement includes a space (an existing database), so it looks something like:

SELECT tblCar.Purchase Price AS Price

SQL doesn't like the space of course. I tried brackets [tblCar.Purchase Price], and single and double quotation marks "tblCar.Purchase Price", as well as tblCar.[Purchase Price], but none of them fly.

Any ideas how to handle a column name with a space in this SELECT statement?

Answer 1

Each unit needs to be in a separate square bracket, when you do something like:

[tblCar.Purchase Price]

It considers this to be complete column name, instead try this [tblCar].[Purchase Price]. Check the following links for more details:

How do you deal with blank spaces in column names in SQL Server?

sql server:what do brackets mean around column name?

How can I reference a column with a space in the name?

In fact you do not need a square bracket for the table name, its required only for the column name with space, so this will also work:

tblCar.[Purchase Price]

Answer 2

In SQL Server 2008 R2, tblCar.[Purchase Price] works fine for me. Which version SQL Version are you using??

Answer 3

In your case, If you are using SQL SERVER you can do this,Wrap the names in square brackets.

It is , however, best to avoid spaces in names if possible

SELECT [tblCar].[Purchase Price] AS Price

Also if anyone gets this problam in MySql than this can be resolved by properly quoting your column names:

SELECT `tblCar.Purchase Price` AS Price

Answer 4

Put both table name and column name in the brackets like that [tblCar].[Purchase Price]. Putting the table name in quotes doesn't work in MS SQL Server.