Check out the regular expression library:
Specifically:
REGEXP_REPLACE?(text, pattern, replace ...)
MarkusQ
2009-02-25 19:34:26
+2
A:
+2
A:
what is my best option for fixing the WHERE condition into something less offensive?
Do the replace in the application layer, there is no call for that logic to be in the database. Make it a plain old PHP function.
ETA: argh, I see what you mean. You're stuffed then, all that's left is “the fact that the database should never have been constructed to require such a select in the first place”! :-) You could move the REPLACE out to a stored production (CREATE FUNCTION)... that would certainly make the query look nicer, but it's kind of sweeping the problem under the carpet really as it still requires the whole table be scanned and processed to make the SELECT query. I don't think you can do a lot better without changing the schema, sorry.
(I'm guessing this is a function to get a ‘cleaned’ ID-style token from a text title? Normally you'd indeed do that in a plain old PHP function, and store it as a separate column from the ‘real’ title. Then you can select it easily, and index it for performance.)
bobince
2009-02-25 19:37:06
How? given that the replace functions are applied against the field in the database, not the PHP variable
Cruachan
2009-02-25 19:48:23
+1, alter table to add filter column and run update with this replace fot all table once. also add replace on insert new records.
Max Gontar
2009-02-25 20:42:41
+1
A:
Oh dear, that's a fun one. Here's a summary of what it does to strSomeField:
- spaces and forward slashes become hyphens
- commas, ampersands, and plus-signs are removed
- converted to lowercase
This can't be easily done in MySQL without adding in the regexp_replace user-defined function that MarkusQ linked, which I believe will require recompliation of MySQL.
Do you have the option of simply processing all the data in the table so that this isn't necessary? Create a PHP script to select all the values in strSomeField, perform the same processing as I summarized above, and update the rows with the new values. Or will this break other parts of the application?
Chad Birch
2009-02-25 20:08:21
A:
If you do create a new field with a preprocessed strSomeField column, you should add a trigger that automatically updates it if strSomeField changes. Might eliminate some headaches.
Matt Kane
2009-02-26 11:57:09
A:
after stripping out characters so it matches what could be previously passed as a URI parameter.
Oh. Same pitfall again and again.
Do not use product name as a key!
Don't you think SO authors are less experienced than you?
But look at the SO question url:
stackoverflow.com/questions/587422/how-can-i-clean-up-this-select-query
They use a numeric key and the rest just for decoration.
So, name can be edited at any time but the page will remain the same. And sure, no problems like yours one.
It's not database problem. It's design problem. Fault I'd say.
Col. Shrapnel
2010-05-31 05:13:19
@col. Yes, in the end I just swept it under the carpet using a function because actually fixing it properly breaks *everything*. The whole system uses a single giant God class and is full of holes like this - at least it's less offensive to look at now and reads cleaner.
Cruachan
2010-06-15 16:35:39