Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Posts
    77

    Talking Unanswered: double quote or single quote?

    Hi to all,

    I want to ask your opinions about what kind of quote to use in appending or using string values in our SQL statements. Is it the single quote or the double quote? For me, I think the double quote is very effective because, it can accept a string value as a whole, even if the string value has a single quote in it, (ex: Toby's), in which we could write it as "Toby's".

    How about the single quote? It generates error if use would replace those double quotes in the example string above, because the string itself has a single quote in it. Putting escape characters would do the trick but isn't it a little hard to track if you are writing a lot/longer SQL statements?

    Maybe there are things that I did not know about? I see many examples in books, using the single quote. Even the dump file of mySQL uses single quotes (correct me if I am wrong)?

    Happy to hear from you guys!
    Thanks and god bless all!

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    if you use double quotes then
    Code:
    select "Toby's ok"       -- will work
    select " "Hello" said Peter"   -- will fail
    if you use single quotes all the time
    Code:
    select 'Toby's ok'       -- will now fail
    select ' "Hello" said John'   -- will now work
    You can either swap from single to double quotes depending on what's in your string but this can look messy. I prefer to use single quotes because when you need to embed your sql in PHP you'll be able to use double quotes in your PHP code to surround the SQL and this in turn allows you to use PHP variables in your SQL. You can also just simply remove the quotes to make life easier

    Code:
    $name = "John";
    $sql = "select 'Hello said $name' ";
    You will of course need to escape any use of the same quotes inside your string whatever though. Quotes are always a minefield and it's best to build up your queries slowly. It gets even more fun in unix shell where backwards quotes run what ever is within them and return the output as the string.

    Mike

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    double quotes work only in mysql, if you should ever have a need to port your database to some other system, your queries will fail

    single quotes are standard sql, whereas double quotes in standard sql are used to delimit table and column names containing special characters

    furthermore, standard sql use two consecutive single quotes to escape a single quote inside a string --
    Code:
    'o''toole'
    mysql supports this use of standard sql (always has), even though it allows other escape characters as well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •