Case Statement(Help Please)

I am trying to use a case statement to pull bad phone numbers out of my
company table into a readable format. I am using SQL Server 2000 SP4.
The phone numbers, in my inherited database, are in any number of formats,
not complete, etc, and I need to pull a report showing only the good phone
numbers.
My Case statement returns the error below the statement.
-- SELECT SUBSTRING((RTRIM(phone) + ' '+
RTRIM(phone) + ' '), 1, 25) AS Name, phone,
Phone =
CASE
WHEN SUBSTRING(phone, 1, 2) > len(10) THEN 'Phone'
WHEN SUBSTRING(phone, 1, 2) < len(10) THEN 'Phone'
END
FROM company
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '+(' to a column of data type int.
RayRay wrote on Fri, 11 Nov 2005 07:05:07 -0800:

> I am trying to use a case statement to pull bad phone numbers out of my
> company table into a readable format. I am using SQL Server 2000 SP4.
> The phone numbers, in my inherited database, are in any number of formats,
> not complete, etc, and I need to pull a report showing only the good phone
> numbers.
> My Case statement returns the error below the statement.
> -- SELECT SUBSTRING((RTRIM(phone) + ' '+
> RTRIM(phone) + ' '), 1, 25) AS Name, phone,
> Phone =
> CASE
> WHEN SUBSTRING(phone, 1, 2) > len(10) THEN 'Phone'
> WHEN SUBSTRING(phone, 1, 2) < len(10) THEN 'Phone'
> END
> FROM company
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value '+(' to a column of data type
> int.
SUBSTRING(phone, 1, 2) > len(10) is the problem
This takes the first 2 characters of the phone column, and compares it to
the length of the string '10' (implicit conversion of the LEN expression to
a string). The first 2 characters are '(+', and due to implicit conversion
will try to convert that to an integer to compare to the value 2.. What
exactly are you trying to achieve here? It makes no sense.
Dan|||I am trying to pull good phone numbers from a list of bad numbers.
For Example:+ () -
+ () -3681
+ () -8544
+ () ?
+ () ?
+ () 0-+00-00
+ () 0-00
+ () 0-00
+ () 0-00--00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-000
+ () 00-000
+ () 011-1-4756-6900
+ () 011-234-1266-89
+ () 011-2711-320-50
+ () 011-2711-377-38
+ () 011-322-545-252
+ () 011-331-4323-20
+ () 011-358-013360
+ () 011-418-643-307
+ () 011-44-002-7551
+ () 011-45-4468-446
+ () 011-468-719-500
+ () 011-495-254-991
+ () 011-603-707-449
+ () 011-612-4655-24
I inhereted this database from someone who just put stuff in anyway they
wanted. Now I need to pull out the ones formatted corrected so they can be
inputted into a new system.
Ray
"Daniel Crichton" wrote:

> Ray wrote on Fri, 11 Nov 2005 07:05:07 -0800:
>
> SUBSTRING(phone, 1, 2) > len(10) is the problem
> This takes the first 2 characters of the phone column, and compares it to
> the length of the string '10' (implicit conversion of the LEN expression t
o
> a string). The first 2 characters are '(+', and due to implicit conversion
> will try to convert that to an integer to compare to the value 2.. What
> exactly are you trying to achieve here? It makes no sense.
> Dan
>
>|||Ray wrote on Fri, 11 Nov 2005 07:39:16 -0800:

> I am trying to pull good phone numbers from a list of bad numbers.
Sorry, I guess I wasn't being clear enough. From the SELECT statement you
provided I can't figure out what you're trying to do. I can only guess that
you're looking for strings of at least 10 characters after the +( at the
start, but as to what you want returned it makes no sense - why would you
display the first 25 characters of the phone number concatentated to itself
as the name column, and then show the phone number again as phone, and then
appear to just put the string 'Phone' into another column called Phone? A
long list of numbers doesn't explain anything. What is a well formatted
phone number? None of those I would consider well formatted, but then again
I'm a UK resident and we have a different number format here.
Dan|||Daniel Crichton wrote:
> Ray wrote on Fri, 11 Nov 2005 07:39:16 -0800:
>
> Sorry, I guess I wasn't being clear enough. From the SELECT statement
> you provided I can't figure out what you're trying to do. I can only
> guess that you're looking for strings of at least 10 characters after
> the +( at the start, but as to what you want returned it makes no
> sense - why would you display the first 25 characters of the phone
> number concatentated to itself as the name column, and then show the
> phone number again as phone, and then appear to just put the string
> 'Phone' into another column called Phone? A long list of numbers
> doesn't explain anything. What is a well formatted phone number? None
> of those I would consider well formatted, but then again I'm a UK
> resident and we have a different number format here.
Another idea thrown into the mix: write a user defined function that
either maps a given phone number to a valid one or maps phone numbers to
something like "ok", "maybe", "crap". Then you can easily select
your_function(phone) = 'ok'.
Kind regards
robert