Is there a way to extract text matching a regular expression from a column in MySQL?

Question

For example, if all entries in a particular column have the form [a-z]+[0-9]+, how can one extract just the leading letters, so that asdf123 and as3456 return 'asdf' and 'as', respectively?

Answer 1

I haven't used it, but to answer your question:

MySQL Regex

From the MySQL Reference page:

SELECT 'abcde' REGEXP 'a[bcd]{1,10}e';

Edit On second thoughts (and as pointed out by Mark below), this will only return whether or not the text is contained in the string. Not the matching portion of the string.

On further searching, this doesn't seem to be available with the basic regex functions of MySQL. Perhaps querying for matching values and then performing comparison outside of MySQL may be the answer?

Answer 2

This is not posible with mysql regex capablities (unless installing some udf's which appear to be designed for that). Technically, as [0-9] is a limited set you could throw 10 LOCATE's in LEAST (watch out for & invalitdate 0), and use SUBSTRING. Not a pretty picture:

SET @var ='asjdasd35433';
SELECT SUBSTRING(@var,1,
  LEAST(
    IF(LOCATE(0,@var)=0,LENGTH(@var),LOCATE(0,@var)),
    IF(LOCATE(1,@var)=0,LENGTH(@var),LOCATE(1,@var)),
    IF(LOCATE(2,@var)=0,LENGTH(@var),LOCATE(2,@var)),
    IF(LOCATE(3,@var)=0,LENGTH(@var),LOCATE(3,@var)),
    IF(LOCATE(4,@var)=0,LENGTH(@var),LOCATE(4,@var)),
    IF(LOCATE(5,@var)=0,LENGTH(@var),LOCATE(5,@var)),
    IF(LOCATE(6,@var)=0,LENGTH(@var),LOCATE(6,@var)),
    IF(LOCATE(7,@var)=0,LENGTH(@var),LOCATE(7,@var)),
    IF(LOCATE(8,@var)=0,LENGTH(@var),LOCATE(8,@var)),
    IF(LOCATE(9,@var)=0,LENGTH(@var),LOCATE(9,@var))
   ) -1 ) as 'result';
+---------+
| result  |
+---------+
| asjdasd | 
+---------+

If you need it look at udf's. Otherwise you're just better of fetching the field and manipulate it outside MySQL.

Edit: if the number fits in an integer, dirty hackery could result in :

SET @var ='asjdasd35433';
SELECT SUBSTRING(@var,1,LENGTH(@var)-LENGTH(CAST(REVERSE(@var) as UNSIGNED)));
+---------+
| result  |
+---------+
| asjdasd |
+---------+

Answer 3

I've not used it myself, but there may be some UDF functions in the lib_mysqludf_preg library that will help.