Re: Quick SQL Question

Yes I tried it both ways as well, with the UPPER and with and still no
records. I tried it with @Paddress= '700' and @PAddress = '3201A' made sure
there were records to find and it did not find them. Still worried about
records where @Paddress = '700' and getting '7001' returned. This has
happened as well, as the record in question has a AddressLow value of '700'
and AddressHigh is '7001' I know it is because they are varchar type just
not sure how to sort numerically as is the desired result.

-----Original Message-----
From: Discussion forum for developers using Windows Forms to build apps and
controls [mailto:[email protected]] On Behalf Of Booth,
Bill
Sent: Wednesday, May 16, 2007 2:14 PM
To: [email protected]
Subject: Re: [DOTNET-WINFORMS] Quick SQL Question

Maybe I screwed up my Outlook code. I just tested this and it works.
Case insensitive.

DECLARE @PAddress CHAR(10)
SET @PAddress = 'w-1'

SET @PAddress = CAST(UPPER(@PAddress) AS varbinary(10)) --<--Or whatever
size it is.

SELECT *
FROM AAA
WHERE @PAddress BETWEEN CAST(UPPER(LowAddress) AS varbinary(10)) AND
CAST(UPPER(HighAddress) AS varbinary(10))

-----Original Message-----
From: Discussion forum for developers using Windows Forms to build apps
and controls [mailto:[email protected]] On Behalf Of
Steve Abaffy
Sent: Wednesday, May 16, 2007 13:17
To: [email protected]
Subject: Re: [DOTNET-WINFORMS] Quick SQL Question

If I cast the fields and variables like you suggest it returns no
records of any kind.

-----Original Message-----
From: Discussion forum for developers using Windows Forms to build apps
and controls [mailto:[email protected]] On Behalf Of
William E. Booth
Sent: Tuesday, May 15, 2007 9:50 PM
To: [email protected]
Subject: Re: [DOTNET-WINFORMS] Quick SQL Question

What happens if you do something like this?
CAST(UPPER(@PAddress) AS varbinary(8))<--Or whatever size it is.


BETWEEN (UPPER(LowAddress) AS varbinary(8)) and (UPPER(HighAddress) AS
varbinary(8))

On Tue, 15 May 2007 15:14:58 -0500, Steve Abaffy <[email protected]>
wrote:

>Yes this is MS SQL Server, version SQL 2000, Not sure what you mean by
>How am I executing the query? I have a stored procedure
>
>The SQL statement from the stored procedure is below:
>Where #tbl1 is a list of all address in the zipcode provided.
>
>
>select
>Zipcode,StreetPreDirection,StreetName,StreetSuffix,StreetPostDirection,
>Add
re
>ssLow,
>AddressHigh,OddEven,SecondAbbrev,SecondLowNo,SecondHighNo,SecondOddEven
>,
>Zip4  into #tbl2 from #tbl1 where SoundEx(streetname) = SoundEx
(@streetname)
>and @PAddress between AddressLow and AddressHigh and OddEven in
>('B',@OddEven) and SecondHighNo IS NULL
>
>-----Original Message-----
>From: Discussion forum for developers using Windows Forms to build apps
and
>controls [mailto:[email protected]] On Behalf Of
>Booth, Bill
>Sent: Tuesday, May 15, 2007 3:09 PM
>To: [email protected]
>Subject: Re: [DOTNET-WINFORMS] Quick SQL Question
>
>Is this MS SQL Server and what version? How are you executing the
query?
>
>
>-----Original Message-----
>From: Discussion forum for developers using Windows Forms to build apps

>and controls [mailto:[email protected]] On Behalf Of
>Steve Abaffy
>Sent: Tuesday, May 15, 2007 14:59
>To: [email protected]
>Subject: [DOTNET-WINFORMS] Quick SQL Question
>
>Hello,
>
>        I am writing an application that looks up addresses. The data
>is broken up into prestreetdirection, streetname, poststreetdirection,
>suffix, lowaddress and highaddress. All these fields are char fields as

>we have lowaddress "numbers" like 312B or 31-A etc...
>I have a query that basically has in its where clause,
>        Where @SuppliedHouseNumber between LowAddress and HighAddress
>
>For the value of @SuppliedHouseNumber='700' the query returns the
>following two records
>1) LowAddress = 700 , highAddress = 700 which of course is expected,
>2) LowAddress = 700 , highAddress = 718 which of course is NOT Expected

>but I don't know why.
>
>Any ideas and also how to get around the other problem of
>SuppliedHouseAddress = 123 and returns like 1123?
>
>Thanks
>
>The information in this e-mail is confidential, may be legally
>privileged and is intended solely for the addressee. If you have
>received this e-mail in error, you are hereby notified that any use,
>distribution, or copying
of
>this communication is strictly prohibited.

The information in this e-mail is confidential, may be legally privileged
and is intended solely for the addressee. If you have received this e-mail
in error, you are hereby notified that any use, distribution, or copying of
this communication is strictly prohibited.