How to replace a string in a column with REGEX

Question

I have a table which references mobile numbers, but they are not all in international format. In the mobile column, I have many records and I need to change just all records with a 1 as first char to 491 and leave the rest unchanged. I tried this:

UPDATE test
SET mobile=(REPLACE (mobile,'1','491'));

But it turns a number like 1512231178 to 4915122349149178. It should be 491512231178. I think I need something like REGEXP '^1' in the REPLACE.

How can I do this with a simple update?

MariaDB's REGEXP_REPLACE()

Rick James
– Rick James

2015-06-27 01:41:23 +00:00
Commented Jun 27, 2015 at 1:41 — Rick James
– Rick James, Commented Jun 27, 2015 at 1:41

Giorgi Nakeuri · Accepted Answer · 2015-06-25 16:14:43Z

3

You can simply do like this:

UPDATE test SET mobile = '49' + mobile
where mobile like '1%'

answered Jun 25, 2015 at 16:14

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

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

2 Comments

Mucki Over a year ago

nice solution - it runs with no error, but 1512231178 becomes 1512231227 it adds 49. mobile is varchar(30) NOT NULL

Giorgi Nakeuri Over a year ago

@Mucki, hmm, it is very strange, are you sure mobile us varchar? try SET mobile = '49' + cast(mobile as varchar(30))

Community · Accepted Answer · 2017-05-23 11:52:30Z

1

Giorgi Nakeuri's clever problem substitution (prefixing for replacement) will work for your particular example. In other cases, where it is impossible to simplify the problem in such a way, you could use the INSERT string function, which does almost exactly what you want: replaces a substring of a specific length at a specific position with another substring. It does not allow you to also specify that the old substring match a specific value but you could use a WHERE clause for that, as Giorgi did:

UPDATE
  test
SET
  mobile = INSERT(mobile, 1, 1, '491')
WHERE
  mobile LIKE '1%'
;

If you needed to parametrise both the old and the new substring, this is how you could go about it:

UPDATE
  test
SET
  mobile = INSERT(mobile, 1, LENGTH(@OldValue), @NewValue)
WHERE
  mobile LIKE CONCAT(@OldValue, '%')
;

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Jun 26, 2015 at 9:06

Andriy M

78k18 gold badges100 silver badges157 bronze badges

2 Comments

Mucki Over a year ago

mysql> SET mobile = CONCAT('49', mobile) -> where mobile like '1%'; ERROR 1193 (HY000): Unknown system variable 'mobile'

Andriy M Over a year ago

SET mobile... isn't supposed to be a separate statement, it's part of the UPDATE actually.

Collectives™ on Stack Overflow

How to replace a string in a column with REGEX

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related