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

[si_the_geek]

The ' character is used in SQL to mark the start or end a string, so the following fails:

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:

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:

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?