use the cast or convert function on a varchar field

I have a varchar column in my table that contains commodity codes.  Most of these codes are numeric and I want to find all records that have a commodity code between 2 and 99.  I have tried to use the following  statment:

Select * from sca
where cast(sca_commcode as bigint) between 2 and 99

This produced an error that stated error converting data type varchar to bigint.

After viewing the records, I see that some of the commodity codes are text entries such as 'Engine'

Is there a way to use cast or convert (or someother function) to return a value of zero if the value is a text entry?  I guess what I am looking for is functionality similar to the VB val function.
dsgvwfAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:

Select * from (
  select * from sca where isnumeric(sca_commcode) = 1
) sq
where cast(sca_commcode as bigint) between 2 and 99
0
 
Patrick MatthewsCommented:
Select * from sca
where isnumeric(sca_commcode) = 1 and cast(sca_commcode as bigint) between 2 and 99
0
 
dsgvwfAuthor Commented:
I have tried both of the above queries and I still get the same error.

I dont know if it makes a difference, but this is an older version of SQL Server running on a Windows 2000 Server.  In Query analyzer if I go to help and about is shows version 8.00.760
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please run this:
Select * from (
  select * from sca where isnumeric(sca_commcode) = 1
) sq

and show some data?
I mean, that does visibly return some data that is numerical or larger than bigint

you first case, you might rty:



Select * from (
  select * from sca where isnumeric(sca_commcode) = 1
) sq
where cast(sca_commcode as float) between 2 and 99
0
 
dsgvwfAuthor Commented:
Here is the output from
Select * from (
  select * from sca where isnumeric(sca_commcode) = 1
) sq


commcodes.xls
0
 
dsgvwfAuthor Commented:
It looks like the values for commodity code is below 100,000.

The second query using the float type also produces the error.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is there no other code?
I cannot believe the code as such can produce this error?




Select * from (
  select * from sca where isnumeric(sca_commcode) = 1
) sq
where cast('0' + sca_commcode  as bigint) between 2 and 99

Open in new window

0
 
dsgvwfAuthor Commented:
I have been away from my office for a while and just had a chance to get back to this.

I think the problem may be the fact that I am unfamiliar with the version of Query Analyzer I am using.  Upon looking closely, I see that there are two tabs at the bottom that say Grid and Message.  When I run the query the message tab is automatically activated with the error message.  If I click on the Grid tab it does in fact have the records I want.

I placed the query in a vb routine and was able to producee the report that I needed.

Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.