|
Home > Archive > MS SQL Server New Users > November 2005 > Filtering bad phone numbers
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Filtering bad phone numbers
|
|
|
| I am using SQL Server 2000 SP4
I am trying to filter bad phone numbers out of my results but I cannot quite
get the syntex that I need. I have phone numbers like:
++ () -
+ () -3681
+ () -8544
+ () ?
+ () ?
+ () 0-+00-00
+ () 0-00
+ () 0-00
+ () 0-00--00
++ () 0-00-000
+ () 00-000
+ () 011-1-4756-6900
+ () 011-234-1266-89
+
++ () 1-00
+ () 1-00
+ () 1-00
+ () 1-00
+ () 1-00-
+ (412) 576-0886
+ (415) 457-4497
and I need to filter them out of my query results. Here is my query
SELECT
a.city as 'City', a.company_name as 'Company Name',
a.comments as 'Description', a.email as 'Email', a.fax as 'Fax',
a.First_name as 'First Name', a.last_name as 'Last Name',
a.Lead_Source_Type as 'Lead Source', a.rn_create_date as 'Legacy Created
Date',
a.lead__id as 'Legacy ID', a.cell as 'Mobile Phone', a.occupation as
'Occupation',
a.first_name + a.last_name as 'Owner ID', RIGHT(a.phone, 17) as 'Phone',
a. product_interest_typ
e as 'Product Interest', a.title as 'Saluation',
a.state_ as 'State/Province', a.address_1 + a.address_2 + a.address_3 as
'Street',
a.title as 'Title', a.www as 'Website', a.zip as 'Zip'
from lead_ a
select phone
from company
where len(phone) != len(17)
As you can see I used Right(a.phone, 17) but it still doesn't filter out the
numbers that are over 17 length or the bad numbers.
Beginning users who fell into a cesspool of bad data.
Thanks.
Ray
| |
| mark sullivan 2005-11-20, 3:23 am |
| Ray,
Can you clarify your request? It is not clear by your examples what a
valid phone number should be.
Mark
"Ray" <ray.smith@loislaw.com> wrote in message
news:19A6A11A-0492-4B9F-BD81- B433B98EF6B2@microso
ft.com...
>I am using SQL Server 2000 SP4
> I am trying to filter bad phone numbers out of my results but I cannot
> quite
> get the syntex that I need. I have phone numbers like:
> ++ () -
> + () -3681
> + () -8544
> + () ?
> + () ?
> + () 0-+00-00
> + () 0-00
> + () 0-00
> + () 0-00--00
> ++ () 0-00-000
> + () 00-000
> + () 011-1-4756-6900
> + () 011-234-1266-89
> +
> ++ () 1-00
> + () 1-00
> + () 1-00
> + () 1-00
> + () 1-00-
> + (412) 576-0886
> + (415) 457-4497
> and I need to filter them out of my query results. Here is my query
> SELECT
> a.city as 'City', a.company_name as 'Company Name',
> a.comments as 'Description', a.email as 'Email', a.fax as 'Fax',
> a.First_name as 'First Name', a.last_name as 'Last Name',
> a.Lead_Source_Type as 'Lead Source', a.rn_create_date as 'Legacy Created
> Date',
> a.lead__id as 'Legacy ID', a.cell as 'Mobile Phone', a.occupation as
> 'Occupation',
> a.first_name + a.last_name as 'Owner ID', RIGHT(a.phone, 17) as 'Phone',
> a. product_interest_typ
e as 'Product Interest', a.title as 'Saluation',
> a.state_ as 'State/Province', a.address_1 + a.address_2 + a.address_3 as
> 'Street',
> a.title as 'Title', a.www as 'Website', a.zip as 'Zip'
> from lead_ a
> select phone
> from company
> where len(phone) != len(17)
>
> As you can see I used Right(a.phone, 17) but it still doesn't filter out
> the
> numbers that are over 17 length or the bad numbers.
>
> Beginning users who fell into a cesspool of bad data.
>
> Thanks.
>
> Ray
>
|
|
|
|
|