Single and double quotes in SQL Server
Sometimes, when executing a query like this:
SELECT * FROM Orders WHERE OrderDate = "01-01-98"
...things are getting wrong in SQL Server. Seems it's trying to get the specified value as a column name, and produces a syntax error. This query instead can be used as a workaround:
SET QUOTED_IDENTIFIER OFF SELECT * FROM Orders WHERE OrderDate = "01-01-98"
SQL Server documentation says that double quote identifiers are valid only if QUOTED_IDENTIFIER is set to ON, which is the default on OLE DB provider and ODBC driver based connections. So when it is ON everything inside quotes is interpreted as a column or table identifier and no as a value.
Other simpler workaround is to use single quotes:
SELECT * FROM Orders WHERE OrderDate = '01-01-98'or
SET QUOTED_IDENTIFIER ON SELECT * FROM Orders WHERE OrderDate = '01-01-98'
Further, single quotes can be escaped with a previous single quote. So supposing that default SET QUOTED_IDENTIFIER is ON, this is ok for selecting using La maison d''Asie
value:
SELECT * FROM Territories WHERE ShipName = 'La maison d''Asie'