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

I want to search for numbers, but some of them have - characters or spaces inbetween them, is there a way to do like a

select numbersonly(phone) from table

?

Than开发者_如何转开发ks.

Include this in your WHERE clause:

WHERE CONVERT(your_column, SIGNED INTEGER) IS NOT NULL 

and augment it with the REPLACE function.

So based on your SELECT statement the updated could look something like:

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

If what you want to do is select a certain text/varchar column, but you want to strip its data of everything but numbers, you have two options to choose from really:

1. Strip non-numerics on retrieval

   Use this query:

   SELECT your_column FROM table WHERE ...
   

   And strip all non-numeric characters as you're retrieving the results from the database programatically.
   
   or...

2. Strip non-numerics pre-insert
   
   As you're inserting the data into the database, strip all characters before the insertion takes place, and use the same query as above for selecting.

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

Good luck!

It kind of seems Lye wants to pull phone numbers from a varchar column. The best way to do this might be to pull the records from the database and do a regex on all non-number characters in the results.

edit: Incrediman makes a good point, stripping unwanted characters from what sounds like a dedicated phone number field would be your best bet when they are being input from the form.

But since there is already information in the table, pull the data, strip unwanted characters with regex and place it on the page in the format you want or do what ever magic you wanted to do with the numerical string.

You'll probably want to use regular expression matching with RLIKE. The documentation is here.