backtick

[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

[marksmithhfx]

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

[shawnblc]

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?

[dave.kilroy]

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

[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

[marksmithhfx]

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

[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

[marksmithhfx]

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

[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

[marksmithhfx]

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

[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