Real Software Forums
http://forums.realsoftware.com/

SQL - ODBC - Escaping characters - double quotes.
http://forums.realsoftware.com/viewtopic.php?f=3&t=47927
Page 1 of 1

Author:  superjacent [ Mon May 20, 2013 8:45 am ]
Post subject:  SQL - ODBC - Escaping characters - double quotes.

I'm writing SQL Update statements using ODBC to an Access database. I have the choice of enclosing the entire string value data in either single or double quotes. I'm preferring single quotes and so far so good.

Within a string data value I can escape out an apostrophe (single quote) by doubling up with another apostrophe. When the Update statement is executed I can confirm by checking within the Access database the the update completed AND I only see a single quote; that is, the double single quote was stripped back to a single quote. This I like.

I escape a double quote by preceding it with an escape backslash character "\". The Update statement is valid and after executing I check the result at the Access database. This time though I can see the escape character as part of the text. I'm not overly happy about this though I know upon retrieving the data I can un-escape the escape character.

For testing purposes, rather than using the escape backslash character to escape double quotes, I doubled up on the double quotes and it made for a valid SQL Update statement. Again though, after execution, checking the data in Access the two double quotes are present. Yes, upon retrieval of the data the additional double quotes can be removed for display purposes.

My query is firstly, is this the norm? Secondly, and more of an observation, if another application ie. Access, is retrieving that data by default the escape character (or additional double quotes) will be present. This doesn't seem quite right to me. Are there any other options available?

Author:  Jym [ Mon May 20, 2013 3:07 pm ]
Post subject:  Re: SQL - ODBC - Escaping characters - double quotes.

I think you need to triple it or use chr(34) it's been too many years since I last used Access but I think if you want


'Bob is a "nice" person'

you would use

'Bob is a """nice""" person' or use chr(34)

Author:  superjacent [ Tue May 21, 2013 2:36 am ]
Post subject:  Re: SQL - ODBC - Escaping characters - double quotes.

I'm querying why the escape character is ultimately embedded in the data.

Author:  timhare [ Tue May 21, 2013 10:04 am ]
Post subject:  Re: SQL - ODBC - Escaping characters - double quotes.

This is from memory so it may be off, but I believe you only have to escape the quote mark you are using to enclose the string. Eg., if you're using single quotes around the string, you don't need to escape the double quote, and vice versa.

Author:  superjacent [ Tue May 21, 2013 4:07 pm ]
Post subject:  Re: SQL - ODBC - Escaping characters - double quotes.

timhare wrote:
This is from memory so it may be off, but I believe you only have to escape the quote mark you are using to enclose the string. Eg., if you're using single quotes around the string, you don't need to escape the double quote, and vice versa.

Thanks Tim, yep that did the trick (and now I remember that from years ago).

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/