4

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

?

Thanks.

Lye
  • 121
  • 2
  • 5
  • 2
    Do you mean that you want to select only numeric columns? Or do you have a varchar column in a table which you'd like to select, but the returned data should be stripped of everything but numbers? – Cam Apr 16 '10 at 21:12
  • Yeah, can you clarify? For instance, if a column contains "5x", do you want to include the 5 (but not the x), or ignore it? Same for "5 x". – T.J. Crowder Apr 16 '10 at 21:13
  • The OP wants to select from a text column but receive only the numerical characters from that column, with non-numerical characters removed. (212) 555-1212 should return as 2125551212, for instance. – Larry Lustig Apr 16 '10 at 21:15
  • Lye, please provide us with some more info so we can help! – Michael Boggess Apr 16 '10 at 21:22
  • incrediman i mean i have a varchar column that i want to strip all the non-numbers out of. i can't go back and change the values in the database, but i want to select before i change the numbers. that is, I want LIKE "415%" to select entries that start with (415) – Lye Apr 16 '10 at 21:32

4 Answers4

3

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
Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
  • 1
    keep in mind phone numbers can be longer than what can fit in an int, if you've network prefixes or a country code in it. – nos Apr 16 '10 at 21:21
1

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.

Michael Boggess
  • 43
  • 7
  • 1
    MySQL supports regexes, but only for comparison - not substring retrieval: http://dev.mysql.com/doc/refman/5.1/en/regexp.html – OMG Ponies Apr 16 '10 at 21:15
  • @mjboggess: +1 - if it is indeed this that the OP is trying to do, this is a good solution (probably - depending on the environment). – Cam Apr 16 '10 at 21:16
1

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!

Community
  • 1
  • 1
Cam
  • 14,930
  • 16
  • 77
  • 128
0

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

Alex Korban
  • 14,916
  • 5
  • 44
  • 55