List:General Discussion« Previous MessageNext Message »
From:Rhino Date:November 17 2005 4:15pm
Subject:Re: A bit of SQL help for a MySQL novice.
View as plain text  
----- Original Message ----- 
From: "Rick Dwyer" <rdwyer@stripped>
To: <mysql@stripped>
Sent: Thursday, November 17, 2005 10:28 AM
Subject: A bit of SQL help for a MySQL novice.


> Hello All.
>
> I am hoping for a bit of help with some code that has really given me 
> some trouble.  If this is not he correct forum for this any help in 
> pointing me to a more suited list would be appreciated.
>
> I have a MySQL 4.1.x database containing records with phone numbers.
>
> Most of the phone numbers are enter in 12035551212 format, but some  are 
> entered with spaces or "-" or "(" or other characters.
>
> I need to read the first 4 positions in the phone number to determine 
> it's location.
>
> My statement looks like this:
> 'Select mid(phone, 1,4) as phoneareacode from phonetable'
>
> This works but if the number is entered as 1(203)-555-1212 the above 
> would return   "1(20" which is not what I am looking for.
>
> Is there a way to have the select statement examine only numeric  values 
> in the phone number so it would disregard the other charcters?
>
> In Lasso, you can use a Replace with a Regular Expression function to 
> have just the digits 0-9 examined but haven't been able find  a way  to do 
> this in SQL.
>
> Any help is appreciated.

You have two basic options:
1. Make the data uniform in format so that it is easily accessed.
2. Let the users input the data in whatever format they like and then try to 
deal with it.

It looks like you have opted for the second choice. If it were me, I'd 
_strongly_ prefer the first choice. I would put edits on the forms or 
applications that prompt the user for the phone number and force the input 
to match one format. For instance, if you prefer to see the phone number as 
one long string of numbers, e.g. 12025551212, either ignore any characters 
they type that aren't digits or strip out the punctuation characters 
afterwards.

By the way, I'm _not_ saying that you should store the numbers as one long 
string; there are other options but I would choose the one that was going to 
be most useful to you based on your business requirements. If the area code 
is going to be important to you, as it appears from your question, it might 
be a good idea to store it in a separate column. For instance, you could put 
the country code (the '1') in a Country_Code column, put the area code in an 
Area_Code column, put the 7 digit number in its own column, and then put the 
extension (if applicable) in yet another column if that would help you. 
Beware of foreign phone numbers though because they don't look like US ones 
(and don't make the mistake of thinking that the '1' at the beginning of the 
phone number automatically means the US; I'm in Canada and our phone numbers 
also start with 1, our area codes are also three digits, and the rest of the 
number is also 7 digits. Phone numbers in Europe or Africa or Asia follow 
rather different patterns that are shared by Canada and the US.)

Now, your input routines _could_ mimic the way you store the phone numbers. 
For instance, if you want separate columns in the database for country code, 
area code, the rest of the number, and the extension (if any), you _could_ 
provide a separate field in your input form for each of those things. 
However, you don't have to do it that way; you could just as well put the 
full phone number in one input field and then split it out when you insert 
it into the database. That's up to you.

But I would definitely use the input routines to force the phone numbers to 
follow whatever pattern you want it to have. It shouldn't be the database's 
job to handle this sort of thing, at least in my opinion. Of course, you'll 
want to fix the data that is already in the database, too. (If there are 
only a few rows in the table, you could do that manually. If not, you could 
write SQL to do it.)

However, if you insist on allowing multiple formats for your phone numbers, 
the String Functions in MySQL should help you. Just look for them in the 
manual: http://dev.mysql.com/doc/refman/4.1/en/index.html (chapter 12).

You may have to use a combination of functions to create new temporary 
versions of the phone number that don't have the punctuation but you can 
probably manage something, although it might be ugly.

Another possibility is that you could write a user-defined function to strip 
the punctuation out of the phone numbers. See 
http://dev.mysql.com/doc/refman/4.1/en/adding-functions.html for information 
about this. Basically, you would write your own function in C or C++ to do 
this work for you, then plug the function into MySQL so that you can use it 
in your SQL. For instance, if you wrote a function called 
STRIP_PHONE_PUNCTUATION() and installed it in MySQL, your query would look 
like this:

'Select mid(STRIP_PHONE_PUNCTUATION(phone, 1,4)) as phoneareacode from 
phonetable'

Your new function would give you a version of the phone number that had no 
punctuation, then the mid() function would find the area code for you.

Rhino



-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 16/11/2005

Thread
A bit of SQL help for a MySQL novice.Rick Dwyer17 Nov
  • Re: A bit of SQL help for a MySQL novice.SGreen17 Nov
  • Re: A bit of SQL help for a MySQL novice.Rhino17 Nov
    • Re: A bit of SQL help for a MySQL novice.Rick Dwyer17 Nov
  • Re: A bit of SQL help for a MySQL novice.Rhino17 Nov
  • Re: A bit of SQL help for a MySQL novice.Peter Brawley17 Nov
RE: A bit of SQL help for a MySQL novice.Mikhail Berman17 Nov