#1
  1. A mule with a spinning wheel.
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Wales
    Posts
    118
    Rep Power
    18

    Question SQL Escape Characters for Access


    Hi all,

    I need to pass the contents of a text box to an Access database, problem is that the textbox could contain any normal character.

    What is the escape character so that apostrophies won't cause the end of the string?

    Cheers

    Dave
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,565
    Rep Power
    4550
    For Microsoft databases, you need to convert single apostrophes to double ones ' '

    You can use

    sql = replace(sql, " ' ", " ' ' ")

    I added some spaces for readability
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jul 2003
    Posts
    1,152
    Rep Power
    17
    Yes!Doug G'anwser is correct!Plz replace the ' to the ''...
  6. #4
  7. A mule with a spinning wheel.
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Wales
    Posts
    118
    Rep Power
    18
    Cheers, but is there an standard rule? Would you always just type the character twice?

    The textbox could hold and ASCII character. " ' ", " & ", " % " etc.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jul 2003
    Posts
    1,152
    Rep Power
    17
    Yes,It's SQL statement rule with using "..U can find some knowledge about it in the sql statement guide!...
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    2
    Rep Power
    0

    Rule for LIKE clauses


    To escape special characters in LIKE clauses, the general rule is to enclose the character in [], e.g.[*] and [?].

    Example:

    select * from x where y like "[*]*"

    will give you all records from table x where the y field starts with an asterisk (*).

    Kind regards,
    Jesper Hertel
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    2
    Rep Power
    0

    All LIKE escapes


    I guess this must be the necessary escapes for LIKE operator arguments:

    [ --> [[]
    ' --> ''
    " --> ""
    ? --> [?]
    * -->[*]
    # --> [#]

    I guess the ']' character does not need escaping, in contrast to the '[' character.

    /Jesper
    Last edited by jhertel; November 13th, 2003 at 10:37 AM.

IMN logo majestic logo threadwatch logo seochat tools logo