MySql Replace and Regex -

i trying remove trailing letters building addresses e.g. 111a.

i can find patterns with:

select address table address regexp '^[0-9]{1,6}[a-z] ' 

however unsure how or if can include regexp inside replace don't have 10*26 different replaces e.g.

update table set address=replace(address,'0a','0') address '%0a %' 

vs sort of (which i've tried):

update table set address=replace(address,regexp ([0-9]{1,6})[a-z],\1)  address regexp '^[0-9]{1,6}[a-z] ' 

can include regexp inside replace?

no, not in mysql. sigh. missing feature.

there's extension this, install need able control mysql server. here: https://github.com/mysqludf/lib_mysqludf_preg#readme