| The Truth About VarChar
By 15 Seconds Discussion List | Rating: 2.7 out of 5
Rate this article |
email this article to a colleague
suggest an article| Ricky Asks: This may sound a little weird, but here we go anyway. I have a field in my
table and it is identified as a varchar. In my asp code if I search for
numbers(on this field) it will not work correctly. I thought a varchar
served mulitple purposes? SELECT * from equip LEFT JOIN COMPANY ON equip.CONTROL = COMPANY.CONTROL
Where advertise='yes' and etype='Levee Plow' AND V1 >= '1' AND V1 <= '2000'
order by model,price David Responds: This is a hazard of using varchar fields for numerics. As far as the ASCII
code is concerned, this is a true comparison: '1' <= 'a' If the field has nothing but numerics in it, consider changing it to an INT
column or create a new column. Of course, not knowing what is in the column
(other data) then the solutions that will be provided may help. You may want to research in SQL BOL Convert() and Cast()
Jamie Adds: I'm not sure if you can use the >=, <= operators with strings. You should probably look into the CAST or CONVERT functions of T-SQL. I'm not sure of the syntax, but pseudo code would loke something like: SELECT * from equip LEFT JOIN COMPANY ON equip.CONTROL = COMPANY.CONTROL
Where advertise='yes' and etype='Levee Plow' AND CONVERT(int,V1) >= 1 AND
convert(int,V1) <= 2000 order by model,price Look in the BOL for more exact info.
Dan Says: Try trimming the field - doesn't VARCHAR pad with spaces?
Nick Replies: VARCHAR means variable character length, and I believe CHAR will become padded with spaces. VARCHAR is a string, text, letters. They are not numbers, digits, figures,
and a field can't be both a number and string! If you're using SQL to get data from a table and you want to do a SELECT on
a field that's a VARCHAR but you need to compare it against a number then
you'll need to convert the VARCHAR field to a number in the SQL statement. So have a look in your SQL Books On Line for CAST and CONVERT. This'll
explain how to do it! http://msdn.microsoft.com/library/psdk/sql/ts_ca-co_2f3o.htm
This conversation string was taken from the 15Seconds ASP Listserv on 2/27/01. If you have an ASP-related question or would like to share some of your knowledge with others, you may join the list by clicking here.
|
| | | Supporting Products/Tools |  | Proposion N2N | | Proposion N2N connects Microsoft .NET applications to Lotus Notes and Lotus Domino databases. This ADO.NET managed data provider allows you to perform blindingly fast queries and updates of Notes data from ASP.NET pages, .NET web services, Windows, or Mobile applications. An innovative SQL-like query language leverages the unique features of Notes and makes collaborative software accessible to relational database programmers. |
[Top] |
|
| | Other Articles |  | Sep 15, 2005 - Building an Image Keyword System | | Unlike text-based file formats image files aren't made up of words, which makes searching for an image file by keyword difficult. Instead of being able to simply open the file to see what it contains, we're stuck looking at the text around it and other metadata to determine the image's meaning. In this article, Ziran Sun shows you how to build a simple database-based image keyword system that allows you to associate keywords with images and use these keywords to make finding images easier.
[Read This Article] [Top] |
| | | Feb 10, 2005 - A Step-by-Step Guide To Using MySQL with ASP.NET - Part 1 | | Back in the days of classic ASP, if you were building a database-driven
web site, your choice was either to invest a lot of money to get a copy of Microsoft SQL Server
(or some other enterprise-ready database) or invest a lot of time finding a way to deal with the
performance and scalability limitations of Microsoft Access. Luckily these days there's
another viable alternative: MySQL.
[Read This Article] [Top] |
| | | | | | | |
| Mailing List
Want to receive email when the next article is published? Just Click Here to sign up. |
|
