CODEWITHSUNDEEP
mysqlregex
user3649739
user3649739

Reputation: 1869

MySql Replace and Regex

I am trying to remove trailing letters from Building Addresses e.g. 111A.

I can find those patterns with:

Select Address from Table where Address Regexp '^[0-9]{1,6}[A-Z] '

However I am unsure how to or if I can include the regexp inside a Replace so that I don't have to do 10*26 different replaces e.g.

Update Table set Address=Replace(Address,'0A','0') where Address like '%0A %'

vs some sort of (which I've tried):

Update Table set Address=Replace(Address,regexp ([0-9]{1,6})[A-Z],\1) 
where Address Regexp '^[0-9]{1,6}[A-Z] '

Upvotes: 2

Views: 7348

Answers (2)

user3649739
user3649739

Reputation: 1869

I have yet to find Regex Replacement but this seemed to work for now. I neglected to mention that these were full Building Address so e.g. `111A Washington St'. This is what I ended up doing:

Update Table set Address=
concat(
left(SUBSTRING_INDEX(Address,' ',1),CHAR_LENGTH(SUBSTRING_INDEX(Address,' ',1))-1),
right(Address,(CHAR_LENGTH(Address)-CHAR_LENGTH(SUBSTRING_INDEX(Address,' ',1))))
)
where Address Regexp '^[0-9]{1,6}[A=Z] '

After spending time on similar challenges like this in this project, my conclusion is that, until and unless regex is integrated into functions such as update,locate,replace that using substring_index and char_length can get you a long way. Please see my similar solution to a follow-up question to this one here: Replace using Locate and/.or regex?

Upvotes: 1

O. Jones
O. Jones

Reputation: 108641

can I include the regexp inside a Replace?

No, not in MySQL. Sigh. Missing feature.

There's an extension to do this, but to install it you need to be able to control your MySQL server. Here: https://github.com/mysqludf/lib_mysqludf_preg#readme

Upvotes: 1

Related Questions