Selecting results by not exact match

Question

I need to figure out the best way to select records from db by a string that's not matching exactly the string in db.

The one stored in db is:

 So-Fi (S. 1st St. District), 78704 (South Austin), Bouldin Creek, South Congress

And the one I have to match with is:

$myArea = 'So-Fi-S-1st-St-District-78704-South-Austin-Bouldin-Creek-South-Congress';

The $myArea is actually a value taken from db and formatted for SEO-friendly URL on a different page.

I've tried

SELECT* FROM t1 WHERE area = REPLACE('".$myArea."', '-', '')

But clearly there's no match. Basically, since I cannot tame $myArea and format it back to what it was in db.

Is there a way to remove all punctuation and such leaving only alphanumerics in db before selecting?

You could use `REPLACE` to remove the punctuation. Would that be enough? Is the string you have to match always in this specific format (just missing the punctuation)? — moni_dragu, Jan 18 '17 at 16:39
Yes, I actually use the string from db on another page to strip down punctuation and special characters to convert it to SEO friendly format, like ...Creek-South-Congress — santa, Jan 18 '17 at 17:37
In this case use a chained replace. See this answer here: http://stackoverflow.com/a/1671056/2022457 — moni_dragu, Jan 18 '17 at 17:51
I'm not sure I can use REPLACE. Technically I need to use REPLACE on db value. Will it be something like: SELECT* FROM t1 WHERE REPLACE(area, '-', '') = REPLACE('".$myArea."', '-', '') — santa, Jan 18 '17 at 18:34

score 1 · Accepted Answer · answered Jan 18 '17 at 20:07

Doing lookups like this will guarantee you some headache, there are to many special cases which you'll be unable to cover.

Why don't you add a "slug" field to your database, where you put the SEO friendly string. This way you do a direct look up on the slug without having to do a lot of string manipulation.

Example of database table:

CREATE TABLE `locations` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(255) NOT NULL,
  `location` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

Then you do lookups like this:

SELECT location from locations where slug = :slug;

Actually it's a very good point. I'll also be able to skip manipulating this into a SEO-friendly URL string. I think it's the angle I was looking for, thanks. — santa, Jan 18 '17 at 20:10

Selecting results by not exact match

1 Answers1