CODEWITHSUNDEEP
mysqldatabase
Rishabh
Rishabh

Reputation: 900

How to extract numbers from a text field in MySQL

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?

Upvotes: 2

Views: 2009

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

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.

Upvotes: 1

jserranm
jserranm

Reputation: 46

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?

Upvotes: 3

Related Questions