hi all,

Here's a nut to crack:

I would like to extract a part of a string stored in a varchar column in a
mysql table.

Since the part of string I want to extract is neither at a fixed position
nor logically separated by a fixed character I was thinking using regexp
would by a good idea

Sample data:
alf eats 2 cats
peter's 50 cards
apple ibook 2345 sold by apple computers
scandlines mariner vessel 345 operated by Nordic Transport

(all these are made up)

What I want to extract is the part of string that is the part that comes
before the first digit ...

using perl i would :

if ($string =~ /^(.*)\d+/) {$wanted = $1;}

now my question :

if testing in sql for :
* * * * column1 regexp '([[:alpha:]]+)[[:digit:]]+([[:alpha:]]+)'
how then can I extract / refer to the first subpattern ([[:alpha:]]+) ?

What I would want is something like (what obviously doesnt work since @1 is
not set):

select
* * * * if (
* * * * * * * * column1 regexp '([[:alpha:]]+)[[:digit:]]+([[:alpha:]]+)',
* * * * * * * * @1,
* * * * * * * * column1
* * * * ) * * *
*

Besides I m running Mysql 5.0.23

Any suggestios welcome
TIA

CVH