Finding small letter between two capital letters - MySQL

Question

I've got problem - I need to find every single phrase like AbC (small b, between two Capital letters). For Example a statement: Little John had a ProBlEm and need to know how to do tHiS.

I need to select ProBlEm and tHiS (you see, BlE and HiS, one small letter in between two capital). How can I select this?

Answer 1

In MySQL you can use a binary (to ensure case sensitivity) regular expression to filter for those records that contain such a pattern:

WHERE my_column REGEXP BINARY '[[:upper:]][[:lower:]][[:upper:]]'

However, it is not so straightforward to extract the substrings which match such a pattern from within MySQL. One can use a UDF, e.g. lib_mysqludf_preg, but it's probably a task more suited to being performed within your application layer. In either case, regular expressions can again help to simplify this task.