Problem solve Get help with specific problems with your technologies, process and projects.

Accepting the ' character in searches

We have a search screen that allows users to search for a party name. Is there any way to get the search screen to accept the character ' as a character in the search screen without messing up the SQL query?

As you are noticing, the single quote is a termination character for a string literal in the SQL language. The following query works just fine:

SELECT * FROM emp WHERE name='Smith';
But the following query does not work fine:
SELECT * FROM emp WHERE name='O'Shea';
There is a mismatched number of single quotes. Luckily, you can escape the middle quote, which you need for your literal. You can do this as follows:
SELECT * FROM emp WHERE name='O''Shea'; 
Notice that I've used two single quotes (not one double quote) in the middle of the string.

For your Web application, you'll have to change any occurrences of a single quote to two single quotes before passing the string to the database. This is typically the job of a simple Javascript routine.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.