CODEWITHSUNDEEP
sqlsql-serversql-server-2005search
jonhobbs
jonhobbs

Reputation: 27972

Search column in SQL database ignoring special characters

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.

Upvotes: 12

Views: 19227

Answers (4)

Neil Menon
Neil Menon

Reputation: 199

This is an old question but I just stumbled upon it and am also working with song titles and want to expand upon the accepted answer that uses REPLACE. You can create a list of the characters you want to ignore and create a simple function in any language to generate the quick'n'dirty never-ending REPLACE lines. For example, in Python:

def sanitize(db_field):
    special_chars = ['•', '"', "\\'", '*', ',']
    sanitized = "REPLACE({}, '{}', '')".format(db_field, special_chars.pop(0))
    for s in special_chars:
       sanitized = "REPLACE({}, '{}', '')".format(sanitized, s)
    return sanitized

A call such as sanitize("name") will return

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(name, '•', ''), '"', ''), '\'', ''), '*', ''), ',', '')

which can be used in your query. Just wrote this so hope it helps someone.

Upvotes: 1

Robin Day
Robin Day

Reputation: 102538

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

Upvotes: 14

Raj More
Raj More

Reputation: 48034

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.

Upvotes: 2

mcandre
mcandre

Reputation: 24662

Use a combination of TRANSLATE, UPPER, and TRIM.

Upvotes: 0

Related Questions