CODEWITHSUNDEEP
mysql
theCowardlyFrench
theCowardlyFrench

Reputation: 228

Escaping a forward slash in an SQL name? It can be "escaped", but SQL believes it to be multiple columns

The last person in my job has flooded column names with special characters such as (?,!, and /), as well as used many reserved keywords for column names (more often than not, timestamp or user is used).

Normally, I step around this by using double quotes or brackets to escape the SQL object. A subset of the full list of columns are below: 

DriverID, 
Department, 
Odometer, 
MerchantState, 
MerchantCity, 
gallons/Units, 
timestamp, 
tax

Inside my query, I wrap the two columns in question (gallons/units and timestamp) inside double quotes. Timestamp because it's a reserved keyword, and Gallons/units, because without the quotes, SQL reads the query, stops at the slash, and tells me "Gallons" is not a column inside the table.

If I do wrap double quotes around the column name, SQL returns a different error: "Operand should contain 1 column(s)".

I've tried every variant (only capturing the slash in quotes, quoting both, using brackets, mixing brackets and quotes, etc. but with to no avail).

Is there anything I can do to fix this query short of renaming the column name and changing the associated code in the program that pulls from it? (the really tedious task I'm trying to avoid).

Upvotes: 2

Views: 21639

Answers (4)

sanjayselvaraj
sanjayselvaraj

Reputation: 23

Put it inside the double quotes.

"gallons/units"

Upvotes: 0

spencer7593
spencer7593

Reputation: 108390

In SQL Server, identifiers can be delimited using square brackets, e.g.

SELECT [gallons/units] ...

In MySQL, identifiers can be delimited using backticks, e.g.

SELECT `gallons/units` ...

(NOTE: If MySQL SQL_MODE includes ANSI_QUOTES, then double quotes are treated as delimiters for identifiers, similar to the way Oracle handles double quotes; absent that setting, double quotes are handled as delimiters for string literals. With ANSI_QUOTES included SQL_MODE, "gallons/units" will be interpreted as an identifier (column name). Without ANSI_QUOTES, MySQL will see it as a string literal, as if it were enclosed in single quotes.)

FOLLOWUP:

As far as an error "operand should contain only 1 column(s)", that's usually a problem with query semantics, not an issue with escaping identifiers.

A subquery in the SELECT list can return only a single expression, for example, this would throw an error:

Query: SELECT 'foo' , ( SELECT 'fee' AS fee, 'fi' AS fi )

Error Code: 1241
Operand should contain 1 column(s)

Upvotes: 4

Zach Calvert
Zach Calvert

Reputation: 11

There are a couple of options. First, have you tried using %/ to escape the slash?

Example: "select * from 'gallons%/units';"

Second one I've found, which may or may not be helpful, is to provide an escape character definition, such as http://blogs.msdn.com/b/zainala/archive/2008/08/17/using-and-escape-clause-in-sql-server-like-query.aspx

select * from MyTable where Description like '|[summary|]%' escape '|'

In your case select * from 'gallons|/units' escape '|'

You indicate both mysql and sql-server in your tags, so I'm not sure which server support I should be looking for exactly.

Upvotes: 0

Alessandro Lai
Alessandro Lai

Reputation: 2274

You can try backticks instead of double quotes

 `gallons/units`

Upvotes: 2

Related Questions