MySql Replace and Regex

Question

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] '

You can find some regex replace function if you search, but it is very limited and useless. I forgot about doing this in MySQL and created a csv which I was parsing with regex in C# and created a new csv file and imported it in MySQL. — Giorgi Nakeuri
– Giorgi Nakeuri, Commented May 28, 2016 at 15:52

O. Jones · Accepted Answer · 2016-05-28 15:51:59Z

1

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

answered May 28, 2016 at 15:51

community wiki

O. Jones

Sign up to request clarification or add additional context in comments.

2 Comments

user3649739 Over a year ago

Thanks that looks like a project and a half though! I posted my temporary solution as an answer for now, the consensus seems to be no regex replace. Lots of little things in MySql I'd change as I start to get to using it more and more...

Rick James Over a year ago

regexp_replace is available in MariaDB.

Community · Accepted Answer · 2017-05-23 12:15:54Z

1

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?

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered May 28, 2016 at 15:58

user3649739

1,8692 gold badges18 silver badges33 bronze badges

Collectives™ on Stack Overflow

MySql Replace and Regex

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related