?
Solved

use the cast or convert function on a varchar field

Posted on 2010-01-08
8
Medium Priority
?
226 Views
Last Modified: 2012-05-08
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.
0
Comment
Question by:dsgvwf
  • 4
  • 3
8 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 26210124
Select * from sca
where isnumeric(sca_commcode) = 1 and cast(sca_commcode as bigint) between 2 and 99
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 26210133
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
 

Author Comment

by:dsgvwf
ID: 26210237
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26210565
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
 

Author Comment

by:dsgvwf
ID: 26210666
Here is the output from
Select * from (
  select * from sca where isnumeric(sca_commcode) = 1
) sq


commcodes.xls
0
 

Author Comment

by:dsgvwf
ID: 26210686
It looks like the values for commodity code is below 100,000.

The second query using the float type also produces the error.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26211028
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
 

Author Comment

by:dsgvwf
ID: 26212329
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question