mySQL question: How do I return a match for a regexp?

Question

I want to know if I can get the variable match returned in mySQL when usinig REGEXP. If I have the text "this is test number 23 and nothing else" in a field and run

where field REGEXP 'number ([0-9]+) and'

I only get 1 or 0, but what I really want is "23", and to be able to use it in let's say "give me all fields > 15".

Yes, that is the problem with REGEXP i guess, that it only returns 1 or 0.

ie I need a solution to my problem, where I wanna know if the digits inside of the text has a certain value and from this determine if it should be included in the result.

Answer 1

what type of field are you using the regular expression on? As far as I could tell reading the manual, regular expressions only work on string based fields (TEXT, VARCHAR, etc.) but probably not on numeric fields (INT, FLOAT, etc.).

also, are you including the word "number" in your regular expression? If I am reading your regular expression properly, it should return any row where field is "number (x) and" replacing the x with any number. If this is what you want you would probably be better off using [[:digit:]]

Also, the REGEXP function only returns true or false (or more accurately 1 - true, or 0 - false).