MySQL regexp

jakeone

This is my first stab at regular expressions. Can anyone tell me what I'm doing wrong. I'm trying to retrieve the first 4 consecutive digits after the word 'admitted'. There is a varying number of letters and digits after the word 'admitted', so I used [:alnum:]* but I'm only interested in the first 4 consecutive digits I find.

So I wrote:

SELECT candidates.candID, candFName, candLName, candRefNo, cvQual FROM candidates INNER JOIN cvs ON candidates.candID = cvs.candID
WHERE cvQual REGEXP 'Admitted[:space:][:alnum:]*[:digit:]{4}'

The query does not return an error but nor did it return any records!

Thanks for any pointers...

Jake

tomhath

Character classes can only be used inside a bracketed expression. The manual is confusing unless you look closely at the examples. Try adding another set of brackets around your character classes:

'Admitted[[:space:]][[:alnum:]]*[[:digit:]]{4}'

But I don't think that's what you want. REGEXP returns True or False if the string matches the pattern or not, it won't return you part of the string (i.e. the four digits)

What is cvQual? If it holds the four digits, this regexp will never match (this will match a string like "Admitted John Doe 1234", but you won't know where in the string the four digits are located).

jakeone

Thanks... I realised my mistake a hour or so after posting 🙂 (Come to think of it, my query is pretty pointless!)

cvQual does hold four digits but it's in a sea of text and so I was trying to extract it... My approach was to try and make the match and just extract the year. I'm no closer to getting there! lol.