Posted by Erland Sommarskog on 07/02/05 18:49

Anton (anton.hopen@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, esquel@sommarskog.se

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

[Back to original message]