ansaurus

Question

Search column in SQL database ignoring special characters

Answer 1

A:

Use a combination of TRANSLATE, UPPER, and TRIM.

mcandre 2009-08-17 17:05:35

Translate in SQL Server?

Raj More 2009-08-17 17:10:03

I just did a search for these functions but can't find any information.

jonhobbs 2009-08-17 17:29:12

The SQL Server equivalents are UPPER, LTRIM, RTRIM. There isn't a TRANSLATE function but you can use multiple REPLACE functions for each of your special characters.

Robin Day 2009-08-17 17:33:56

So, can I use a REPLACE on a column name, like this.... SELECT Artist, Title FROM Songs WHERE Artist.REPLACE('#','').REPLACE('*','') LIKE '%Keywords%';

jonhobbs 2009-08-17 17:38:46

Answer 2

+1 A:

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.

Raj More 2009-08-17 17:06:40

I am terrible at using Regex's but may be able to find a regex on line which removes special characters. Thanks

jonhobbs 2009-08-17 17:30:07

Answer 3

+6 A:

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

Robin Day 2009-08-17 17:14:02

Dang, beat me to it. This really sounds like the way to go, IMO. In fact I'm facing a similar situation right now and I'm just about to switch to full-text searching.

Steve Wortham 2009-08-17 17:19:51

Unfortunately, the reason I am investigating the possibility of writing our own queries for this is that we currently use full-text indexing and the client doesn't like it because they say it returns too many irrelevant results!

jonhobbs 2009-08-17 17:28:42

@Jon, have you looked into this? http://msdn.microsoft.com/en-us/library/cc879245.aspx

Steve Wortham 2009-08-17 18:00:42

ansaurus

tags:

views:

answers:

Search column in SQL database ignoring special characters

related questions