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

Question

I want to find numbers, but some of them have - characters or spaces between them, is there a way to do something like

select numbersonly (phone) from the table

?

Thanks.

Answer 1

Include this in your WHERE clause:

WHERE CONVERT(your_column, SIGNED INTEGER) IS NOT NULL 

and add it using the REPLACE function.

So, based on your SELECT statement, the updated might look something like this:

 SELECT phone FROM table WHERE CONVERT(REPLACE(REPLACE(phone, '-', ''), ' ', ''), BIGINT) IS NOT NULL 

Answer 2

It seems that Lye wants to infer phone numbers from the varchar column. The best way to do this may be to pull records from the database and make a regular expression for all non-number characters in the results.

edit:. Incrediman makes a good point of removing unnecessary characters from what sounds like a dedicated field of phone numbers would be a better choice when they are entered from the form.

But since the table already has information, pull out the data, separate the unwanted characters with a regular expression and put them on the page in the format you want, or do what ever was the magic you wanted to do with a number string.

Answer 3

If you need to select a specific text / varchar column, but you want to strip its data of everything except numbers, you have two options:

• Read odd numbers while searching

  Use this query:

   SELECT your_column FROM table WHERE ... 

  And remove all non-numeric characters when you programmatically retrieve the results from the database.
  
  or...

• Read odd numbers without first entering
  
  When you insert data into the database, separate all the characters before inserting and use the same query as above to select.

Depending on your situation, one of the above may be more appropriate than the other.

Good luck

Answer 4

You probably want to use regex with RLIKE. The documentation is here .