How to extract numbers from a text field in MySQL

Question

I have a column in MySQL table which has phone numbers, but in unclean format i.e. there are double quotes, dashes and brackets, for ex:

[ "-", "7736562159" ]
[ "8177985242", "-" ]

I want to extract only the 'phone number' part from this string. Is there any mysql function to do it?

http://stackoverflow.com/questions/2756736/how-to-only-select-numeric-data-from-mysql — Pekka, Aug 24 '15 at 10:36
possible duplicate of [MySQL: how to remove all non-alpha numeric characters from a string?](http://stackoverflow.com/questions/6942973/mysql-how-to-remove-all-non-alpha-numeric-characters-from-a-string) — Craig van Tonder, Aug 24 '15 at 10:36
It seems that several solutions are provided here : http://stackoverflow.com/questions/978147/how-do-you-extract-a-numerical-value-from-a-string-in-a-mysql-query — Simon Duflos, Aug 24 '15 at 10:37
Some jobs are best handled in application level code. I would suggest that this is one of them! — Strawberry, Aug 24 '15 at 10:46

score 3 · Accepted Answer · edited May 23 '17 at 12:24

3

Yo could replace the non-numeric elements from the column using a query like

SELECT 
   REPLACE(REPLACE(REPLACE(`phone`, '-', ''), ' ', ''),'"','') AS `phone_number`
  FROM `table`;

Ref: Is there a way to select only integers of a field in mysql?

edited May 23 '17 at 12:24

Community

1
1

answered Aug 24 '15 at 10:44

jserranm

46
2

score 1 · Answer 2 · answered Aug 24 '15 at 10:46

Here is a brute force way, that should do what you want:

select (case when substring_index(col, '"', -2) + 0 > 0
             then substring_index(substring_index(col, '"', -2), '"', 1)
             when substring_index(col, '"', -3) + 0 > 0
             then substring_index(substring_index(col, '"', -3), '"', 1)
             when substring_index(col, '"', -4) + 0 > 0
             then substring_index(substring_index(col, '"', -4), '"', 1)
             when substring_index(col, '"', -5) + 0 > 0
             then substring_index(substring_index(col, '"', -5), '"', 1)
         end)

This breaks on the double quote character and tests if the value is a number. If so, it then extracts it as a string.

How to extract numbers from a text field in MySQL

2 Answers2