CODEWITHSUNDEEP
mysql
Bogdan
Bogdan

Reputation: 1901

Is there a way to get only the numeric elements of a string in mysql?

I'm looking to make it easier for the clients to search for stuff like phone/mobile/fax numbers. For that to happen I want to strip both the search value and the relevant columns in my database of any non-numeric characters before comparing them. I'm using these functions to get only the numeric elements of the strings in mysql but they slow my queries down to a crawl when I use them.

Is there any way to do it without blowing my run times sky high?

Upvotes: 1

Views: 102

Answers (2)

0xCAFEBABE
0xCAFEBABE

Reputation: 5666

The best way around these runtimes, if you have access and permission to do so, is to add a new column with the content you're filtering. Add a WRITE trigger to fill the column with the stripped values, run a script that updates the field once for all records. Add an index and include the new column. Then, in your application, use the new column for searches for a number value of a telephone. Downsides are table schema alterations and added code for the business logic and/or data abstraction layer.

Upvotes: 2

ziad-saab
ziad-saab

Reputation: 20219

The reason why your query times are exploding is because any use of such functions disables you from using any index. Since you are not searching directly on a field, but on the output of a function, there is no way mySQL can use an index to execute the query.

This is in addition to the fact that you have to compute the function output for each record.

Upvotes: 2

Related Questions