regular expression in mysql select query

Question

How can we use regular expressions in select queries? For example:-

select regex(column1, regular_expression) from table1

I found REGEXP being used in where clause but cannot find something for selects like above.

For example, phone_number column in database has (845) 545 5545, the select query should return 8455455545

SELECT * from table1 WHERE column1 REGEXP 'regular_expression'? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Apr 7, 2020 at 14:57
To be very specific I have phone numbers in different formats in phone_number column but would like to select only numbers out of it. For example: phone_number column has (845) 545 5545, the select query should return 8455455545 — Abhishek K
– Abhishek K, Commented Apr 7, 2020 at 15:04

Bill Karwin · Accepted Answer · 2020-04-07 15:45:18Z

1

You need MySQL 8.0 for this:

mysql> select regexp_replace('(845) 545 5545', '[() ]', '') as phonenumber;
+-------------+
| phonenumber |
+-------------+
| 8455455545  |
+-------------+

answered Apr 7, 2020 at 15:45

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

1 Comment

Rick James Over a year ago

And MariaDB 10.0.

Leo · Accepted Answer · 2020-04-07 15:30:40Z

0

Instead of regex you can try replace

SELECT Replace(Replace(Replace(column1,')',''),'(',''),' ','') FROM table1

answered Apr 7, 2020 at 15:30

Leo

1782 gold badges2 silver badges11 bronze badges

Collectives™ on Stack Overflow

regular expression in mysql select query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related