[thelist] mysql query - replacing characters

Jeremy Weiss eccentric.one at gmail.com
Tue Aug 4 11:38:22 CDT 2009


> James O'Donnell uttered:
> 
> Why do you add a space in the concat and then remove it in the replace?

Umm.... umm... hmm... Temporary insanity?

> rudy spouted:
> 
> there is an alternate strategy, which is to store a sanitized value in an
> additional column alongside street_num and street_name, that already has
the
> characters removed, which you would do when adding the row, using proper
> regex in your application language

I wish I could. Unfortunately it's not an option, I'm not allowed to modify
the structure of the db on this one.

> it will

right as usual
 
> please show the query that produced the error

Well, you see... I took a nap and tried it again... amazing how much easier
it was afterwards. :)

Here's the finished product in case anyone else ever needs to filter
everything except letters and numbers:

SELECT unique_id,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPL
ACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT(street_num,''
, street_name),'
',''),'-',''),'/',''),'#',''),'.',''),'"',''),'\\',''),'\'',''),',',''),'{',
''),'}',''),'(',''),')',''),'&',''),':',''),'+',''),'@',''),'=',''),'_',''),
'!',''),'%',''),'^',''),'*',''),'`',''),'~',''),'|','') AS address FROM
listings HAVING address LIKE '%123EasyStreet%'


I wonder what it would take to get a regex replace function into a future
version MySQL?

-jeremy




More information about the thelist mailing list