CODEWITHSUNDEEP
phpmysql
TX Turner
TX Turner

Reputation: 167

In mysql, how can I select the numeric portion of a field?

I have a table containing free-form phone numbers (e.g. '123-456-7890', '(987) 654-3210', or '123.456.7890') and I'm searching them with a numeric string- e.g., '123456'.

Right now, I'm having to do a bunch of replace(replace(replace(.. ad nauseum functions to strip out errant non-numeric characters then use that 'LIKE' the search value to search.

Anyone know of a simpler way to select only certain characters from a field based on type, not position?

Upvotes: 0

Views: 172

Answers (3)

Jay Blanchard
Jay Blanchard

Reputation: 34416

You can use regex in MySQL - 

SELECT * 
FROM `table` 
WHERE `phone` REGEXP '^[12345]'

Here is a working example.

Upvotes: 3

Ryan Willis
Ryan Willis

Reputation: 624

Try splitting up the number into three different values, i.e.

$first = '124';
$second= '456';
$third = '7890';

"WHERE (phone_number LIKE '%$first%' and LIKE '%$second%' and LIKE '%$third%')"

I'm not certain this is as effective as SQL's REGEXP, but it should work since every part is sectioned out. May not work if a number is like this in the db:1245667890 because you can find 124,456, and 7890, but the number does not match. This answer assumes there will never be a solid number.

Upvotes: 0

Peyman.H
Peyman.H

Reputation: 1952

try using regexes. if you want to extarct numbers from a string you can use this:

$string = '123-456-7890';
preg_match_all('/\d+/', $string, $matches);
echo $matches;    //will result in only digits

Upvotes: 0

Related Questions