Regular Expressions

computer_nuke

Hey guys,

I really need help...

How do you construct a regular expression statement give a set of string values, such that you want each value to appear at least once, separated by zero or more characters, and the values can appear in any order?

For example, given the following set of strings
'a'
'b'
'c'

The following will return true

'a b c'
'b a c'
'a XXX XXX c XXX b'
'XXX c XXX a b'
etc etc...

Thanks in advance for the help guys.

Weedpacket

/^(?=.*a)(?=.*b)(?=.*c)/

computer_nuke

so the REGEXP would be

string_being_tested REGEXP '/(?=.a)(?=.b)(?=.*c)/' ?

Thanks

Weedpacket

If your DBMS - whatever it is - supports full PCRE regular expressions. If not, the the equivalent regular expression would be exponentially long (length O(n!) in the number of strings to search for: e.g. "a.b.c|a.c.b|b.a.c|b.c.a|c.a.b|c.b.a") and it would be better to either just have a bunch of LIKEs all ANDed together or see if something could be done with full-text search or about the data format.

computer_nuke

Thanks for your reply.

I'm actually using MySQL and I looked around and it doesn't seem to support ther PCRE Regexp.

It keep returning an error like:

"repetition-operator operand invalid'

I think this is because in MySQL, ? is a repetition operator representing zero or one instances of the character it follows, so in this case "(", but in MySQL that is also used for grouping, so it got confused.

Do you know what I should do in this instance, other than getting the PCRE compatible package installed?

NogDog

There may be a cleaner way, but worst case you could do:

. . . WHERE column_name REGEXP 'a.*(b.*c|c.*b)|b.*(a.*c|c.*a)|c.*(a.*b|b.*a)'

Then do some performance testing to see if that's better or worse than:

. . . WHERE column_name LIKE '%a%' AND column_name LIKE '%b%' and column_name LIKE '%c%'

Weedpacket

NogDog wrote:
There may be a cleaner way, but worst case you could do:

You could; but it would still grow (faster than) exponentially. For four strings the expression becomes

a.*(d.*(b.*c|c.*b)|b.*(d.*c|c.*d)|c.*(d.*b|b.*d))|b.*(a.*(b.*c|c.*d)|d.*(a.*c|c.*a)|c.*(a.*d|d.*a))|c.*(a.*(b.*d|d.*b)|b.*(a.*d|d.*a)|d.*(a.*b|b.*a))|d.*(a.*(b.*c|c.*b)|b.*(a.*c|c.*a)|c.*(a.*b|b.*a))

And for five strings the pattern becomes five times longer than that. Not only that but, however you write it, if the number of strings can vary you also get the fun of constructing it (which, to be honest, isn't that difficult, but is it worth it?).

computer_nuke

The way that I have it right now is actually something like:

. . . WHERE column_name LIKE '%a%' AND column_name LIKE '%b%' and column_name LIKE '%c%'

except instead of the LIKE's, I used regular expressions for each of those.

It does grow very fast (factorially) no matter which of the two ways you do it, which is not good because if you search many keywords, it will crash inevitable. So obviously, both of these cases should not be used.

What do you suggest I can do in this situation, when I'm using MySQL, when I want to search entries where all keywords being inputted must all appear, but in any order?

Also, do you know what I can do to rank my results? such as, if 'a', 'b', 'c,' appears more times, show those results first and then those results with less occurrences?