Search column in SQL database ignoring special characters

Question

Does anybody know if it's possible to do a %LIKE% search against a column in a SQL Server database but get it to ignore any special characters in the column?

So, for example if I have a column called "songs" and they contain the following...

---

Black Or White

No Sleep 'till Brooklyn

The Ship Song

Papa Don't Preach

---

If the user searches for "no sleey till brooklyn" then I would like it to return a match even though they forgot to include the apostrophe. I would also like it to return the 4th row if they search for "SOUL". I'm sure you get the idea....

Any help would really be appreciated.

Answer 1

I would look into using a Full Text Index and then you can use the power of FREETEXT and CONTAINS to do your search.

EDIT: I would still look into refining the Full Text Index searching, however, to follow on from another answer, this is an option using REPLACE.

SELECT
    Artist,
    Title
FROM
    Songs
WHERE
    REPLACE(REPLACE(REPLACE(Artist, '#',''), '*', ''), '"', '') LIKE '%Keywords%'

Answer 2

You will have various characters to remove. Single quotes, double quotes, hyphens, dots, commas, etc.

You can use Regular expressions in your where clause and do a match on the clean value. Read more about regex within SQL here.

As for the art where you want to return the 4th row for SOUL.. you will need a a data structure to tag songs and you will have to search on the tags for the match. I'm afraid we will need more details on your data structure for that.