Ivy Ivy - 1 month ago 6
SQL Question

SQL Searching for String in DB with Special Character/s

I'm trying to do a search in the database with special characters, specifically string with apostrophe.

For example, I want to search for the string: "Sandy's dog", but I just entered "sandys dog" leaving out the apostrophe. Even though "Sandy's dog" exists in the database, it doesn't seem to show it in the results.

Here's my query:

SELECT * FROM `Table` WHERE `Title` LIKE '%sandys dog%'


I have searched everywhere and I can't seem to find a solution that works.

EDIT
Limitations: the string is user generated
Notes:
- If a user searches for sandy's dog with the apostrophe, it works fine as expected.
- Ultimately I would like to get all possible results, if the table contains both strings with and without apostrophe.

Answer

In SQL server, you can use REPLACE:

SELECT * 
FROM Table
WHERE REPLACE(Title, '''', '') LIKE '%sandys dog%'

The double-apostrophe inside the string is an escape character, so it finds any apostrophes in the string and replaces them with blank strings.

Comments