> 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.

"Daniel Crichton" wrote:

> Ray 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
>
>
>

> 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.