Professional Web Applications Themes

wildcards and less/more specific matches in data - MySQL

Hi. We're looking to be able to rate phone calls using an SQL lookup, but we're not sure how we can deal with both finding the most specific match and with wildcards in the data itself. For example, a table might include something like: telephone_number first_minute_rate add_minute_rate 212 7 7 2128764132 0 0 213 7 7 5551212 50 0 So, based on the above, if someone called a phone number in area code 212 they would normally be charged 7 cents per minute. But if they call our customer service number (2128764132) they aren't charged anything, and if they call ...

  1. #1

    Default wildcards and less/more specific matches in data

    Hi. We're looking to be able to rate phone calls using an SQL lookup,
    but we're not sure how we can deal with both finding the most specific
    match and with wildcards in the data itself.

    For example, a table might include something like:

    telephone_number first_minute_rate add_minute_rate
    212 7 7
    2128764132 0 0
    213 7 7
    5551212 50 0

    So, based on the above, if someone called a phone number in area code
    212 they would normally be charged 7 cents per minute. But if they
    call our customer service number (2128764132) they aren't charged
    anything, and if they call directory assistance in any area code
    (5551212), then they are only charged 50 cents for the call.

    Is there some way we could structure a query to return this kind of
    information, or will we have to do many many queries to rate each call?

    Any ideas would be welcomed.

    Thanks...
    Jim Guest

  2. #2

    Default Re: wildcards and less/more specific matches in data

    >Hi. We're looking to be able to rate phone calls using an SQL lookup, 

    A query like:

    ... where '9005551212' like telephone_number
    where telephone_number may contain % or _, could get you your
    possible matches. The next problem is to figure out which row to
    use when you get multiple matches. You might try "most specific
    digits matched". A cheap way (but requires manual maintenance) to
    do that would be to add a match_count column with the number of
    specific digits in the pattern. Use the row(s) with the highest
    match count. (ORDER BY match_count desc LIMIT 1, provided you can
    somehow get rid of all possibilities of multiple matches where the
    result isn't what the first row says).

    Now, what happens if you still get ties? And is giving the priority
    to the longest match what you want? The match_count could be used
    to tweak this in a limited way (say, make it 10*digits matched +
    fudge factor, to break ties between two equal-length matches.
    If all matches are with fixed digits at the same end, this problem
    won't happen.

    Beware of scammers who dial things like:

    190022222225551212
    with the idea of getting an expensive call for free by tacking 5551212
    on the end of it. Also deal with short sequences like 911 and 411.

    Gordon Guest

Similar Threads

  1. Sychronizing User Specific Data
    By RosadoJoseph in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: December 17th, 03:39 AM
  2. Querying data that matches in two different tables
    By weswhite7 in forum Coldfusion Database Access
    Replies: 3
    Last Post: July 28th, 03:53 PM
  3. array data matches but array created in loop doesn't work
    By Reed Law in forum PHP Development
    Replies: 1
    Last Post: August 13th, 04:25 AM
  4. Finding Specific Data
    By Antonio in forum Microsoft Access
    Replies: 2
    Last Post: July 14th, 02:52 PM
  5. deleting specific data after a set time
    By dan in forum Microsoft Access
    Replies: 0
    Last Post: July 9th, 09:28 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