Page 1 of 1
backtick
Posted: Mon Mar 03, 2014 12:19 am
by marksmithhfx
Hi, I use "quote" marks in my SQL code so that I can use non-standard characters for identifiers,
Code: Select all
&"'Epidural PCEA' text, 'Spinal' text, 'Combined Spinal/Epidural' text, 'General Anesthetic' text, 'Other Pain Mgmt' text)" into tSQL
replace "'" with quote in tSQL -- replace single quote with double quote
This works fine in SQLite but mySQL is complaining, I think, because I am using regular " quote marks around the identifiers. The mySQL documentation says that the identifier quote symbol is the backtick (ascii code 96). Since I don't even see a backtick on my MacBook Pro, does anyone know how I can change "quote" in the above statement to a backtick?
Thank you.
Mark
Re: backtick
Posted: Tue Mar 04, 2014 2:12 am
by marksmithhfx
marksmithhfx wrote:does anyone know how I can change "quote" in the above statement to a backtick?
Mark
Code: Select all
replace "'" with numtochar(96) in tSQL -- replace single quote with backtick
Works
Re: backtick
Posted: Tue Mar 04, 2014 9:07 am
by shawnblc
marksmithhfx wrote:Hi, I use "quote" marks in my SQL code so that I can use non-standard characters for identifiers,
Code: Select all
&"'Epidural PCEA' text, 'Spinal' text, 'Combined Spinal/Epidural' text, 'General Anesthetic' text, 'Other Pain Mgmt' text)" into tSQL
replace "'" with quote in tSQL -- replace single quote with double quote
This works fine in SQLite but mySQL is complaining, I think, because I am using regular " quote marks around the identifiers. The mySQL documentation says that the identifier quote symbol is the backtick (ascii code 96). Since I don't even see a backtick on my MacBook Pro, does anyone know how I can change "quote" in the above statement to a backtick?
Thank you.
Mark
If you're on a Macbook Pro Retina, isn't it the key right below the escape key, upper left hand corner?
Re: backtick
Posted: Tue Mar 04, 2014 1:10 pm
by dave.kilroy
shawnblc wrote:If you're on a Macbook Pro Retina, isn't it the key right below the escape key, upper left hand corner?
On a UK Apple keyboard it's in the bottom-left, between the SHIFT and 'Z' keys
Re: backtick
Posted: Tue Mar 04, 2014 1:36 pm
by Klaus
Hi Makr,
now you have found the right key, I would like to post two VERY handy function
that I use in my projects, they will save you a LOT of typing and errors
1. Put (double) quotes around a given string:
Code: Select all
function q tString
return QUOTE & tString & QUOTE
end q
2. Single quotes, escpecially handy for building SQL strings:
Code: Select all
function q2 tString
return "'" & tString & "'"
end q2
You get the picture
Best
Klaus
Re: backtick
Posted: Wed Mar 05, 2014 3:28 pm
by marksmithhfx
Klaus wrote:
1. Put (double) quotes around a given string:
Code: Select all
function q tString
return QUOTE & tString & QUOTE
end q
2. Single quotes, escpecially handy for building SQL strings:
Code: Select all
function q2 tString
return "'" & tString & "'"
end q2
You get the picture
Klaus, thank you, those will come in very handy. I can even add a q3 now that uses a backtick! And Shawn, yes, thank you for pointing that out. I had not recognized it as such.
Cheers,
Mark
Re: backtick
Posted: Wed Mar 05, 2014 3:38 pm
by Klaus
Hi Mark,
I think MySQL will also accept SINGLE quotes -> q2
Isn't a backtick ` actually an ACCENT and not any kind of quote?
Best
Klaus
Re: backtick
Posted: Thu Mar 06, 2014 5:20 am
by marksmithhfx
Klaus wrote:I think MySQL will also accept SINGLE quotes -> q2
Isn't a backtick ` actually an ACCENT and not any kind of quote?
Hi Klaus, here is the SQL reference page I used
http://dev.mysql.com/doc/refman/5.0/en/identifiers.html. You'll note on this page it says
The identifier quote character is the backtick (“`”):
mysql> SELECT * FROM `select` WHERE `select`.id > 100;
But it also says there is a set option called ansi_quotes which allows you to use "quotes" instead of backtick. But ansi_quotes has side effects I did not understand so rather than fool with that (and besides, I was not sure if I would have permission to execute a "set" command on the on-rev server) I opted to try and get this backtick thing working. numtochar(96) worked like a charm, as would the ` char on the keyboard (once I located it). I did just test with 'single' quotes and it gave me a syntax error, but thanks for suggesting to test that anyway. Sometimes we learn something new or obvious.
Anyway, more fun in programming land. There is always something to sweat over
Mark
Re: backtick
Posted: Thu Mar 06, 2014 1:42 pm
by Klaus
Hi Mark,
oh, thanks, did not know that!
But I have always been using single quotes with MySQL in the past without having made
any settings to the database, it just worked
Best
Klaus
Re: backtick
Posted: Sun Mar 09, 2014 3:19 pm
by marksmithhfx
Klaus wrote:But I have always been using single quotes with MySQL in the past without having made
any settings to the database, it just worked
Thanks Klaus. I wonder why the single quote marks don't work on the On-Rev server? I'll send a tech support request for info to see if that is something they can setup differently on their end. It would be nice for the SQLite code and the mySQL code to run the same without having to have difference versions.
Cheers,
Mark
Re: backtick
Posted: Mon Mar 17, 2014 7:41 pm
by phaworth
I believe the official SQL standard says that single quotes are used around text strings and double quotes are used around SQL identifiers such as table and column names. For example:
SELECT "column1" FROM "tablea" WHERE "pkey"='abc'
Of course you don't need the double quotes around table/column names unless they are an SQL reserved word or contain special characters.
Different dialects of SQL have included other options. As you've found, the backtick is accepted by muSQL and MSAccess and SQL Server will accept square brackets around strings. SQLite accepts any of those options and will even bend the rules to accept single quotes instead of double and vice-versa depending on context. If there's any chance that an app might switch from one SQL implementation to another at some point, it's best to stcik with the standard.
Pete