SQL Query Syntax Error - Spaces in Field Names

Question

The database my application uses has field names containing spaces. I believe this to be the cause of my problem. Here is a typical query:

SELECT * FROM 'OV2 BAS' AS bas 
INNER JOIN 'OV2 RefID' AS ids ON 'bas.Ref ID' = 'ids.Ref ID' 
WHERE ids.ENUM_H = 'TDischarge';

How do I deal with the spaces in the field names? Thanks.

Additional Information

This is to access a database made with MS Access 2007 (Microsoft.ACE.OLEDB.12.0).

Porch

This is to access a database made with MS Access 2007 (Microsoft.ACE.OLEDB.12.0). – Porch

Answer 1

I don't think you can use quotes around the actual table name; only the name you assign to it. I would wrap the table in brackets instead: [OV2 BAS]

You also can't put quotes around your joining syntax either. Try this instead:

SELECT * FROM [OV2 BAS] AS bas INNER JOIN [OV2 RefID] AS ids ON bas.[Ref ID] = ids.[Ref ID] WHERE ids.ENUM_H = 'TDischarge';

Downatheel

Answer 2

Replace ' with

• postgreSQL, Oracle: "
• MySQL `
• SQL-server: [ and ]

For example: "OV2 BAS", bas."Ref ID" = ids."Ref ID", etc.

Cineraria

+1 as delimiting tablename and column name together definitely seems wrong. Though I'm guessing backticks rather than quotes based on the delimiter used in the question. – Epenthesis

Answer 3

For Microsoft Access, wrap field names that contain spaces with back ticks e.g. SELECT `Eng Units` FROM Table

Chlorine

Answer 4

That depends on the database engine you're using.
For SQL Server, you have to put the field names in brackets: [ ]

SELECT * FROM [OV2 BAS] AS bas 
INNER JOIN [OV2 RefID] AS ids ON bas.[Ref ID] = ids.[Ref ID]
WHERE ids.ENUM_H = 'TDischarge';

Towardly

This is to access a database made with MS Access 2007 (Microsoft.ACE.OLEDB.12.0). – Porch

Answer 5

You don't specify which DBMS you're using, but I'm guessing SQL server, so

SELECT *
FROM [OV2 BAS] AS bas
     ^^^^^^^^^

... enclose the field name in brackets. Using quotes as you are, turns the field name into a plain string, which will NOT be treated as a fieldname by SQL server.

Stigmatize

This is to access a database made with MS Access 2007 (Microsoft.ACE.OLEDB.12.0). – Porch

Ok. brackets'll work there too. Consider renaming your fields so there's no spaces in them if you can - spaced names will cause pain down the road (as you're finding now). – Stigmatize

You'll have to unquote the field names on the JOIN clause as well - quoting field names turns them into strings, and they WON'T be treated as field names. ` ... ON bas.[Ref ID] = ids.[Ref ID]` – Stigmatize

Why do you say "spaced names will cause pain down the road"? As long as you quote your fieldnames (which you should always do), it will be fine. – Souvaine

@Souvaine "As long as you quote, you should be fine" That's exactly the problem. Not everybody does. Marc B is correct: putting spaces in field names is not best practice. – Shul