Search column in SQL database ignoring special characters

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%'

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.