[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