Ivy Ivy - 1 year ago 68
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.

Limitations: the string is user generated
- 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 Source

In SQL server, you can use REPLACE:

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download