Ls,

I want to make a select query.
This query has to match on column x like:
SELECT *
FROM `table`
WHERE `column` LIKE %value%

I only get some of the results I want because the `table` column has
"unwanted" characters in it.

In PHP I could strip the unwanted characters like this:
$table = preg_replace("/\W/", "", $table);

So I thought I could do something like:
SELECT *
FROM `table`
WHERE preg_replace("/\W/", "", `column`) LIKE %value%

But with the php code converted to "MySQL code".
Unfortunatally this has proven to difficult for this (almost) stupid
noob.

Could anyone of you help me with the select query?

Alexander

In article <8546a170-a6b2-41fa-ab58-cc0db891d19c (AT) j25g2000yqa (DOT) googlegroups.com>, Electricdummy <electricdummy (AT) hotmail (DOT) com> wrote:
Perhaps if you'd be good enough to explain:
1. What you've already tried
2. What happens when you try it
3. What, exactly, are the "unwanted characters" you need to ignore, and where
do they occur?

On 1 jan, 05:00, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:
1. actualy nothing except some small tests on parts of the query in
MySQL since I already knew it wouldn't work.
In PHP I did the select with a regex and it worked (slow) and the
table is growing.
2. I got no rows or all rows back or in most cases an error.
3. The "unwanted characters" are all non-alfanumeric charactars, so
the only wanted characters are: A-Z, a-z and 0-9.
The "unwanted characters occur "at random", so not on a specific place
in the field (that would have been way too easy) and in some cases not
at all.

A "special" thing I tried but actually didn't want to do is make an
extra column with "the same field" stripped from the "unwanted
characters", this worked (including filling them with the data through
a query) except for filling them with a trigger (triggers seems not to
be allowed by the provider). Since I have no control over the input I
cannot change that script to fill the extra field.

Alexander

El 30/12/2010 18:22, Electricdummy escribió/wrote:
If I recall correctly, there isn't a MySQL equivalent to PHP's
preg_replace(). You can use regular expressions to perform matches but
no replacements. However, if you can produce an addecuate expression
that should be enough:

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Whatever, it's going to be very slow.

Assuming you have a valid reason to make "tool" match with "into old",
my advice is to add a secondary column you your table to serve as
pseudo-index and use your client-side language to keep it updated every
time you insert or update a row.



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--