I am trying to query a name (Daniel O'Neal) in column names tblStudents
in an Access database, however Access reports a syntax error with the statement:
Select * from tblStudents where name like 'Daniel O'Neal'
due to the apostrophe in the name.
How do I overcome this?
1 Answer. The single quote or apostrophe is the special character in SQL which specifies the beginning and end of string data i.e. to use it as part of your literal string data you need to escape the special character.
You want to use a string insteaed. To put an apostrophe in a string literal you use double apostrophes. Show activity on this post. String sql="select lastname from employee where FirstName like '%"+firstName.
You escape '
by doubling it, so:
Select * from tblStudents where name like 'Daniel O''Neal'
Note that if you're accepting "Daniel O'Neal" from user input, the broken quotation is a serious security issue. You should always sanitize the string or use parametrized queries.
When you include a string literal in a query, you can enclose the string in either single or double quotes; Access' database engine will accept either. So double quotes will avoid the problem with a string which contains a single quote.
SELECT * FROM tblStudents WHERE [name] Like "Daniel O'Neal";
If you want to keep the single quotes around your string, you can double up the single quote within it, as mentioned in other answers.
SELECT * FROM tblStudents WHERE [name] Like 'Daniel O''Neal';
Notice the square brackets surrounding name. I used the brackets to lessen the chance of confusing the database engine because name is a reserved word.
It's not clear why you're using the Like comparison in your query. Based on what you've shown, this should work instead.
SELECT * FROM tblStudents WHERE [name] = "Daniel O'Neal";
Escape the apostrophe in O'Neal
by writing O''Neal
(two apostrophes).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With