1

I found the following code which removes everything from a php var other than the letters and numbers, perfect!

$string = "remove ever^&thing but *&^*&%£ letters & numbers*&^*";
$cleansedstring = preg_replace('#\W#', '', $string);
echo $cleansedstring;

But I want to query a column in mysql using the same rule. The aim is to kinda remove all ampersands, apostrophes, hyphens etc from the equation.

Right now I use some ugly REPLACE statements!

AND Replace(Replace(Replace(Replace(Replace(MYColumn, '&', ''), '-', ''), ' ', ''), '(', ''), ')', '') =

I would like a bullet proof query going forward that only looks at numbers and letters!

:)

EDIT: I guess I should explain my problem further, perhaps one of you can suggest something else....

I generate links across my website by pulling out long company names and cleaning them up for a seo friendly URLs. So say we have "Bill & Ben's Flower Pot/Garden Service" my eventual URL to their page looks like /bill-bens-flower-pot-garden-service.htm

That URL is a rewritten URL so the actual page is like company.php?name=bill-bens-flower-pot-garden-service

I need to grab "name" and query the database to return their company details. But getting:

bill-bens-flower-pot-garden-service

to return the details of:

Bill & Ben's Flower Pot/Garden Service

Isn't always bullet proof it seems.

It just feels like my code is a little messy in how many replaces I'm doing all over the place. My theory was to just strip all non letters and numeric data out and compare the strings that way:

WHERE Company = 'billbensflowerpotgardenservice' - but I can't seem to do that on the field name itself?

Any suggestions!

James Wilson
  • 809
  • 3
  • 14
  • 25
  • 1
    MySQL doesn't have built-in reg-replace functionality. You might check the answers to this question: http://stackoverflow.com/q/986826/264628 – BrianS Aug 15 '12 at 17:00
  • Just to clarify, you want to modify column data so that "THIS -> so cool!" becomes "THIS so cool" for use in comparing with a plain simple text string? Something like (a nonexistant) REGEXP_REPLACE(...) = "THIS so cool"? – BrianS Aug 15 '12 at 17:07
  • You could also consider a loosey-goosey FULLTEXT index, then use additional filtering in script. – BrianS Aug 15 '12 at 17:09
  • 1
    Also, for completeness. You can compile it in: http://www.regular-expressions.info/mysql.html – BrianS Aug 15 '12 at 17:10
  • I've edited and addded extra info about problem – James Wilson Aug 16 '12 at 08:17
  • OK I've had a change of heart. I've introduced a UrlAlias column, like Slug in WordPress that I'll use to 100% define the url of each company. No dodgy SQL comparisons!!! – James Wilson Aug 16 '12 at 09:40
  • That seems like a good choice. When your parameters aren't working for you one option (so long as your code isn't too entrenched) is always to change the parameters. I think the modification makes sense. – BrianS Aug 16 '12 at 13:55

1 Answers1

0

You can use regular expressions since mysql 5.1. Here is the documentation page for that: http://dev.mysql.com/doc/refman/5.1/en/regexp.html

SDwarfs
  • 3,189
  • 5
  • 31
  • 53
  • 1
    Yesbut ... he wants to modify the column then use the modified text for comparison. I don't see a way this could be done using REGEXP. Now, if you could write your comparison string as a regular expression you'd be golden. (But that's not very user-friendly.) – BrianS Aug 15 '12 at 17:08