Re: Quick SQL Question

You are comparing Text, you should add spaces or zeros on the left and make
all the columns the same length to be able to compare them. For example,
make them all 10 chars long:

SELECT *
FROM AAA
WHERE REPLICATE(' ',10-LEN(@PAddress))+@PAddress BETWEEN REPLICATE('
',10-LEN(LowAddress))+UPPER(LowAddress) AND REPLICATE('
',10-LEN(HighAddress))+UPPER(HighAddress)


_______________________________________________________
Joel Paula
Scientia - Análise e Desenvolvimento de Software, Lda.
+351 - 93 355 87 01 :] www.scientia.pt [:
_______________________________________________________
-----Original Message-----
From: Steve Abaffy [mailto:[email protected]] 
Sent: quinta-feira, 17 de Maio de 2007 14:06
Subject: 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.