Results 1 to 1 of 1

Thread: Database - How do I put the ' character into an SQL string?

  1. #1

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Database - How do I put the ' character into an SQL string?

    The ' character is used in SQL to mark the start or end a string, so the following fails:
    Code:
    Select * 
    From myTable
    Where surname = 'O'Brien'
    This is because the database engine thinks that you want the string to end after the letter O.


    To put the ' character into an SQL string you need to use twice as many, so in this case we would add one extra, eg:
    Code:
    Select * 
    From myTable
    Where surname = 'O''Brien'

    If you are getting the data from any source that could contain the ' character (such as input from your users) you should always use the Replace function to format the data, eg:
    Code:
    Dim mySQL as String
      mySQL = "Select * From myTable Where surname = '" & Replace(Text1.text, "'","''") & "'"

    Note however that the ' character is not the only issue, so doing this has only been a partial fix to the problem. A better solution (which deals with all of the other issues too) is to use Parameters.

    For an explanation of the issues they solve and the code needed, see the FAQ article Why should I use Parameters instead of putting values into my SQL string?
    Last edited by si_the_geek; Dec 30th, 2008 at 05:00 AM.

Posting Permissions

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



Click Here to Expand Forum to Full Width