Friday, September 30, 2011

How to handle One apostrophe in SQL string

If you are using SQL strings in your database operations, chances are you have come across problem with strings in SQL statement. One apostrophe screws up the SQL string, causing the SQL statement to fail.

Assume you have a SQL string:
SELECT * FROM tUsers WHERE LastName='O'Brien';
When LastName contains a name like O'Brien, the final string becomes


The second ' ends the SQL statement, leaving the rest of the string dangling. The solution is easy: Replace each ' in O'Brien with two 's, something like O''Brien, so the final SQL looks like:

SELECT * FROM tUsers WHERE LastName='O''Brien';
The second ' "escapes" the third ', thus leaving the whole SQL statement complete.

No comments: