regexp help

Mucello

I'm writing a MySQL query and need just a little bit of help with regular expressions... specifically the MySQL function REGEXP().

I want to have a where clause that pseudo-coded, looks like this:
... WHERE columnA REGEXP('can be 1, or A, or R') ...

In other words, I want it to pull all instances where columnA contains either 1, A, R or a combination of them.

Thanks
-Mark

mrhappiness

SELECT *
FROM table
WHERE columnname NOT REGEXP '[^1AR]'

should give you entries with 1AR
A1R
AR1
A
R1
RA
AARRR11AR1A
...

but not if there's any other character present

Mucello

Thanks-- very interesting. I just need to sit down and go through a tutorial on regular expressions.

Would this syntax work?
...column REGEXP([1AR])...

Would that also give me any records that contain those three characters or combination thereof?

ultraslacker

You do not need the 'NOT' as it would exclude any column with an 'A', 'R' or 1

mrhappiness

Originally posted by ultraslacker
You do not need the 'NOT' as it would exclude any column with an 'A', 'R' or 1

REGEXP '[^AR1]' will find every column that does contain something different from A, R and 1

since that is not what is wanted the NOT is neccessary to negate the result
=> returns all entries that do NOT contain letters that are different from A, R or 1

--
edit:

i just realized that mucello removed my ^
that is wrong since now you are looking for entreies where there is an A, R or 1 somewehre in the column but "FOOBAR" will be returned as well, since it contains an A and even an R...

Mucello

Originally posted by mrhappiness
i just realized that mucello removed my ^
that is wrong since now you are looking for entreies where there is an A, R or 1 somewehre in the column but "FOOBAR" will be returned as well, since it contains an A and even an R... [/B]

Actually that's exactly what I want to do. I want to find all records where A, R, or 1 are in the field regardless of how many times or combinations.

ultraslacker

Yes, I didn't see the carrot. Thanks for the clarification.

mrhappiness

Originally posted by Mucello
I want to find all records where A, R, or 1 are in the field regardless of how many times or combinations.

and you want to find all records that contain any combination of A, R and 1 but no other characters?

if yes you can copy&paste my SELECT

if you want to find al records that contain A, R, or 1 but may contain other caracters too you have to remove the caret ^and the NOT

if the latter one os correct i misunderstood "contains either 1, A, R or a combination of them", sry

Mucello

Originally posted by mrhappiness
if the latter one os correct i misunderstood "contains either 1, A, R or a combination of them", sry

That's what I want-- the latter is indeed correct. It doesn't matter what other characters are in the field, so long as 1, A, or R are in it.

I appreciate your help on this. It's confusing stuff, no doubt!:o

Mucello

I'm stilll learning with this regular expression business. One more thing...

I want to now find instances where the field contains R and anything else EXCEPT U

So "ROUGH" would be false but "ROTTEN4" would be true. What would be the regexp() syntax for something like that?