How to regexp replace mySQL column before matching?

Question

How can I use regular expressions in MySQL to rewrite the column value to be matched with an exact string? I can only find guides that do the opposite.

SELECT * FROM customers WHERE regexp_replace('([^0-9])', '', phone) = '0123456789';

The reason is that the column can contain all kinds of formatting e.g. "012-345 6789" "(0)12-3456789" and so on...

Please note: This is NOT a question about how data should better be stored. But wheither regexp replaces are possible or not. The example is only demonstrative to simplify the question and it's nature.

Shouldn't this check be applied while entering values in database ? That would save the pain of checking it while retrieving. — user2705585
– user2705585, Commented Mar 6, 2016 at 13:44
Don't use the database to store formatted values. Formatting should be done by functions upon retrieval, either in MySql or PHP. — trincot
– trincot, Commented Mar 6, 2016 at 13:49
In the MySQL documentation I found this: "The REPLACE function does not support regular expression so if you need to replace a text string by a pattern you need to use MySQL user-defined function (UDF) from external library, check it out here MySQL UDF with Regex." — tim
– tim, Commented Mar 6, 2016 at 13:59
Thank you for all your feedback that a better shot is to keep the input data to database in a consistent format. I would like the thread to focus on whither or not there is a regexp_replace possibility in MySQL regardless of the actual cases or examples. — tim
– tim, Commented Mar 6, 2016 at 14:04
What version are you using? Only MariaDB has regexp_replace(). — Rick James
– Rick James, Commented Mar 7, 2016 at 2:47

alexander.polomodov · Accepted Answer · 2016-03-06 13:56:13Z

2

You can improve your application using this 2 steps:

write migration which convert you data with different formats to one canonical
move formatting of this values to your view layer

This approach gives you:

ease in searching by this field
flexibility in using different formats for this field in differents views

answered Mar 6, 2016 at 13:56

alexander.polomodov

5,54414 gold badges44 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

How to regexp replace mySQL column before matching?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related