Professional Web Applications Themes

select only numeric characters - MySQL

I have a table with a day_phone field. Sometimes the numbers are stored as 123-456-7890 sometimes there 1234567890. I want users to be able to search the database, so they can search for 1234567890 and the row will be returned weather it has -'s in it or not. select day_phone from table where day_phone LIKE '[user_input]%' I could do it in the PHP side, but that will be much slower. Ive looked into REGEXP, but that only returns 1 or 0. I just need to return the numbers from a field, without changing the data in the database. Thanks!...

  1. #1

    Default select only numeric characters

    I have a table with a day_phone field. Sometimes the numbers are stored
    as 123-456-7890 sometimes there 1234567890. I want users to be able to
    search the database, so they can search for 1234567890 and the row will
    be returned weather it has -'s in it or not.

    select day_phone from table where day_phone LIKE '[user_input]%'

    I could do it in the PHP side, but that will be much slower. Ive looked
    into REGEXP, but that only returns 1 or 0.

    I just need to return the numbers from a field, without changing the
    data in the database. Thanks!

    nathan@factory8.com Guest

  2. #2

    Default Re: select only numeric characters

    >I have a table with a day_phone field. Sometimes the numbers are stored 

    mysql> select * from t;
    +--------------+
    | a |
    +--------------+
    | 1234567890 |
    | 123-456-7890 |
    | 123-456-7891 |
    | 1234567891 |
    +--------------+
    4 rows in set (0.00 sec)

    mysql> select * from t
    -> where replace(a,'-','')=replace('1234567890','-','');
    +--------------+
    | a |
    +--------------+
    | 1234567890 |
    | 123-456-7890 |
    +--------------+
    2 rows in set (0.00 sec)


    Regards
    Dimitre


    Radoulov, Guest

  3. #3

    Default Re: select only numeric characters

    On 8 Nov 2006 10:21:19 -0800, com wrote: 

    Why not fix (that is, standardize) the data in the table? The last time
    I worked with phone numbers, I built the database to store them from the
    "+" on, and then had the application display it localized to the user.

    --
    35. I will not grow a goatee. In the old days they made you look diabolic.
    Now they just make you look like a disaffected member of Generation X.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  4. #4

    Default Re: select only numeric characters

    I saw the replace in the doentation, but it said that it worked as
    in insert and would change the data in the database.
    http://dev.mysql.com/doc/refman/4.1/en/replace.html

    However, it dosnt appear it would do that in the example you have
    given. Is there anywhere in the doentation that gives you
    information on using the replace in a where clause?

    nathan@factory8.com Guest

  5. #5

    Default Re: select only numeric characters

    >I saw the replace in the doentation, but it said that it worked as 

    replace (statement) != replace (string function)


    REPLACE(str,from_str,to_str)

    Returns the string str with all occurrences of the string from_str replaced
    by the string to_str. REPLACE() performs a case-sensitive match when
    searching for from_str.

    http://dev.mysql.com/doc/refman/4.1/en/string-functions.html


    Regards
    Dimitre


    Radoulov, Guest

  6. #6

    Default Re: select only numeric characters


    Radoulov, Dimitre wrote: 
    >
    > replace (statement) != replace (string function)
    >
    >
    > REPLACE(str,from_str,to_str)
    >
    > Returns the string str with all occurrences of the string from_str replaced
    > by the string to_str. REPLACE() performs a case-sensitive match when
    > searching for from_str.
    >
    > http://dev.mysql.com/doc/refman/4.1/en/string-functions.html
    >
    >
    > Regards
    > Dimitre[/ref]

    Thank you all very much!

    nathan@factory8.com Guest

Similar Threads

  1. Select delimited characters within db field
    By etman in forum Coldfusion Database Access
    Replies: 4
    Last Post: October 8th, 03:52 PM
  2. Replies: 1
    Last Post: December 7th, 10:52 AM
  3. Replies: 8
    Last Post: September 26th, 06:07 PM
  4. Select with numeric conditions in the where part
    By Cowboy \(Gregory A. Beamer\) in forum ASP Database
    Replies: 1
    Last Post: July 14th, 04:25 PM
  5. Replies: 3
    Last Post: July 9th, 10:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139