2

I am trying to use MySql REGEXP to find rows where green and 2012 occurs in the column

I am using .+ in the regexp.

This works:

select  'green 2012-01' REGEXP 'green.+2012'

returns 1

but if I place the '2012' first it returns 0

select  'green 2012-01' REGEXP '2012.+green';

returns 0

I am using MYSQL Software version: 5.1.43 - MySQL Community Server (GPL)

ghoti
  • 45,319
  • 8
  • 65
  • 104
David Mirwis
  • 21
  • 1
  • 1
    This is probably an [XY problem](http://mywiki.wooledge.org/XyProblem). What are you *really* trying to do? – ghoti Nov 07 '12 at 19:21

2 Answers2

2

Regular expressions are kinda order dependent. What you'll need to do is put an | (or) operator between your two items to make it look for either one.

select 'green 2012-01' REGEXP '(green.*2012)|(2012.*green)'
raykendo
  • 620
  • 1
  • 12
  • 25
  • 1
    **+1** - this is correct, though you don't really need all those brackets. – ghoti Nov 07 '12 at 17:55
  • 1
    Thanks for that - I tried it - worked but then I removed the space and it didn't work - select 'green2012-01' REGEXP '(green.+2012)|(2012.+green)' – David Mirwis Nov 07 '12 at 18:01
  • You'll want to use .* (0 or more somethings) instead of .+ (1 or more somethings). I've edited my answer to reflect the changes. – raykendo Nov 07 '12 at 18:14
  • How would I search for three strings e.g 'green' and '2012' and 'London' – David Mirwis Nov 08 '12 at 12:16
0

As an alternative to REGEX, while potentially less efficient, you could simply use LOCATE twice.

SELECT * FROM table WHERE LOCATE('2012', column) AND LOCATE('green', column);
Jason McCreary
  • 71,546
  • 23
  • 135
  • 174
  • I am dynamically constructing the query using an input box to get the the search string. The user would enter 20012^green and I convert it to 2012.+green. It works when green^2012 is entered – David Mirwis Nov 07 '12 at 17:44
  • Are you ultimately searching for each string the user enters? – Jason McCreary Nov 07 '12 at 17:50
  • I concatenate the columns of a table and run a query searching for the string the user has entered e.g green^2012. The user would be trying to find a row that has both green and 2012 in it. – David Mirwis Nov 07 '12 at 17:57
  • `LOCATE` is still applicable in this case. The logic for parsing user input is beyond SQL. – Jason McCreary Nov 07 '12 at 18:09