Lot of times, we come across strings that contain Apostrophe (') like David o' Sullivan, and we may have to do search operation or insert/update operation with them.

When you try to insert a string that contains apostrophe using the insert statement,
insert in to Names(FirstName) values('David o'Sullivan'), the insert statement fails. This is because, the string is split into two parts with the first part containing 'David o' and the second part Sullivan'. There is no starting apostrophe for the second part of the name and hence the insert failed.
Modify the insert statement as insert in to Names(FirstName) values('David o''Sullivan')
and it works fine.

Same is the case with search queries, when you want to find an entry for David o' Sullivan in the table, the search query should be like this:
select * from Names where FirstName like 'David o'' Sullivan' and you get the exact entry.

The same issue occurs when you try doing a select operation on a DataTable in C#.Net. When you try executing the statement,
DataRow []ResultSet = DataTableName.Select("FirstName = 'David o'Sullivan'"); the operation fails.

Now do the following:

string SearchString = "David o'Sullivan";
SearchString = SearchString.Replace("'","''"); //Replace the single apostrophe with double
DataRow []ResultSet = DataTableName.Select("FirstName = '" + SearchString + "'");

The statement is executed and the result set returned if the name exists.