Need Query to Select VarChar Rows that can convert to Integer

[Anton]

I have a table of zip codes, some with Canadian zips. I'd like to take
a zip code and search for nearby zips. For example:

Dim theZip As Integer = textbox1.text

....Parameter.Add(@ziplow, SqlDbType.Int, 5).Value = (theZip - 10)
....Parameter.Add(@ziphigh, SqlDbType.Int, 5).Value = (theZip + 10)

SELECT * from ZIPCODES where Cast(zip_code as Integer) BETWEEN @lowzip
AND @highzip

Problem is the letters in the Canadian records cannot be cast as
integers for this process. I get this error:

Syntax error converting the varchar value '53151 1' to a column of data
type int.

Is there a SQL query that can exclude if no cast can be made to an
Integer?

Thanks!

Anton

[Erland Sommarskog]

Anton (an*********@gmail.com) writes:

I have a table of zip codes, some with Canadian zips. I'd like to take
a zip code and search for nearby zips. For example:

Dim theZip As Integer = textbox1.text

...Parameter.Add(@ziplow, SqlDbType.Int, 5).Value = (theZip - 10)
...Parameter.Add(@ziphigh, SqlDbType.Int, 5).Value = (theZip + 10)

SELECT * from ZIPCODES where Cast(zip_code as Integer) BETWEEN @lowzip
AND @highzip

Problem is the letters in the Canadian records cannot be cast as
integers for this process. I get this error:

Syntax error converting the varchar value '53151 1' to a column of data
type int.

Is there a SQL query that can exclude if no cast can be made to an
Integer?

Not really. You can add a condition like:

AND zip NOT LIKE '%[^0-9]%'

to specify that you only want numeric values. However, you cannot instruct
the optimizer to check this condition before anything else.

In any case, I would assume that you want these non-digit zip codes as
well. So why not this:

SELECT col1, col2, ... FROM ZIPCODES
WHERE zip_code BETWEEN ltrim(str(@lowzip)) AND ltrim(str(@highzip))

although this is a little devilish. Say that the user specified 53141.
Should "53151 1" be included or not? Maybe this is better:

...Parameter.Add(@ziphigh, SqlDbType.Int, 5).Value = (theZip + 11)

SELECT col1, col2, ... FROM ZIPCODES
WHERE zip_code >= ltrim(str(@lowzip)) < ltrim(str(@highzip))

Disclaimer: I know nothing about Canadian zip codes.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

The call it a "Postal Code" in Canada, not a ZIP code. And they are
logically and legally different things, so they ought to be in separate
tables.

ZIP codes are CHAR(5), or CHAR(9) for ZIP+4, not numerics. Programmers
coming to SQL from BASIC still think that they are dealing with a
interpreted language.

Finally, you are doomed anyway. To find physically contigous ZIP
codes, math will not work; you need a look up table or to add
(longtitude, latitude) pairs to your data. You need to Google Melissa
Data and get some of their mailing list software.

CREATE USAMailings
(..
zip_code CHAR(5) NOT NULL
CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]',

);

Remember to use French characters and British spelling checks!!
CREATE CANMailings
(..
postal_code CHAR(7) NOT NULL
CHECK (postal_code LIKE '[A-Z][0-9][A-Z]-[0-9][A-Z][0-9]',
..
);

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

Finally, you are doomed anyway. To find physically contigous ZIP
codes, math will not work; you need a look up table or to add
(longtitude, latitude) pairs to your data. You need to Google Melissa
Data and get some of their mailing list software.

How true. I recently was out one night and met with two friends, and one
complained that I used the wrong zip code (or "postnummer" as well call
it over here) went I sent him post cards. I had used 171 38, when the
correct code was 169 35. I had the same zip code listed for the other
guy, so I asked that too was wrong. No, his zip code was really 171 38.

They live across the street from each other.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Anton]

Celko:

Good advice, but I'm limited to an XML web service feed. Anyway, this
got me closer:

"SELECT * FROM <table> where (isNumeric(zip_code) = 1) and
(Cast(zip_code as Integer) BETWEEN Cast(@lowzip as Integer) AND
Cast(@highzip as Integer))

Needless to say, once it started working it confirmed your doomsday
premonition - math will not work to find contigous ZIP codes.

I'll need to relate this query to another look up table. Thanks for the
feedback.

Anton

[Erland Sommarskog]

Anton (an*********@gmail.com) writes:

Good advice, but I'm limited to an XML web service feed. Anyway, this
got me closer:

"SELECT * FROM <table> where (isNumeric(zip_code) = 1) and
(Cast(zip_code as Integer) BETWEEN Cast(@lowzip as Integer) AND
Cast(@highzip as Integer))

It appears that you have already realised that this query is dead for
other reasons, but permit me to point out a couple of techcnial
problems with it:

1) There is no guarantee on evaluation order, so you could still get a
conversion error.
2) isnumeric is virtually useless. It returns 1 if the string can be
converted to any numeric data type, but just because the string can
be converted to float or money, does not mean that it can convert to
integer. The expression NOT LIKE '%(^0-9]%' is good for finding
positive integers.
3) When you place a column in an expression live above, this means that
any index on that column will not be useful. (In this case: the index
would be sorted in string order and not numeric order.)


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp