Less than, equal to Operator question

[Jeremy Howard]

Hello everyone,

I'm not a database guru so I'm sorry if this is a dumb question but here it
goes...

I have this sql query that I'm trying to run against a table on a Sql 2k
server:

SELECT *
FROM ChangeTable
WHERE (CompanyName >= 'Ad%') AND (CompanyName <= 'L%')

What I'm trying to do is get a range of data where the CompanyName column
is greater than or equal to and also less than or equal to certain values.
The problem I'm having is with the less than, equal to operator (<=); it
doesn't seem to evaluate to true when the CompanyName column is in an equal
state. For example if I have this data:

rowId CompanyName
1 Advair
2 Abus, Inc.
3 Lohan Bros.
4 Moop, Inc.

Running the query from above I would expect (with my feeble database mind)
to get back two records (rowId 1 and 3) but I currently only getting back
one record (rowId 1).

My question is this. Is the above query even valid in sql? Also can the %
wildcard be used with the <= operator when dealing with varchar columns?
Thank you for any help with this issue.

[Hugo Kornelis]

Re: Less than, equal to Operator question

On Thu, 12 Aug 2004 22:34:25 GMT, Jeremy Howard wrote:
[color=blue]
>Hello everyone,
>
>I'm not a database guru so I'm sorry if this is a dumb question but here it
>goes...
>
>I have this sql query that I'm trying to run against a table on a Sql 2k
>server:
>
> SELECT *
> FROM ChangeTable
> WHERE (CompanyName >= 'Ad%') AND (CompanyName <= 'L%')
>
>What I'm trying to do is get a range of data where the CompanyName column
>is greater than or equal to and also less than or equal to certain values.
>The problem I'm having is with the less than, equal to operator (<=); it
>doesn't seem to evaluate to true when the CompanyName column is in an equal
>state. For example if I have this data:
>
> rowId CompanyName
> 1 Advair
> 2 Abus, Inc.
> 3 Lohan Bros.
> 4 Moop, Inc.
>
>Running the query from above I would expect (with my feeble database mind)
>to get back two records (rowId 1 and 3) but I currently only getting back
>one record (rowId 1).
>
>My question is this. Is the above query even valid in sql? Also can the %
>wildcard be used with the <= operator when dealing with varchar columns?
>Thank you for any help with this issue.[/color]

Hi Jeremy,

The '%' can be used in <= comparisons, but it won't have the same meaning
it has in a LIKE comparison. It will simply compare the ASCII value of
whatever string is on the other side of the <= to the ASCII value of the %
sign and return true or false.

If you want all companies starting with Ad, all companies starting with L
and all companies that are alphabetically ordered between them, try

WHERE CompanyName >= 'Ad'
AND CompanyName <= 'LZZZZZZZZZZZZZ ZZZZ'

or

WHERE CompanyName BETWEEN 'Ad' AND 'LZZZZZZZZZZ'

or

WHERE CompanyName >= 'Ad'
AND CompanyName < 'M'

(note I used less than, not less then or equal to - this also means I
can't use the BETWEEN equivalent anymore)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Jeremy Howard]

Re: Less than, equal to Operator question

Hugo,

Thank you for your reply. Your solution works flawlessly. Thanks again.

Jeremy

"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
news:41tnh09i5t 0ogns334gp63v8g 4feeigtnb@4ax.c om...[color=blue]
> On Thu, 12 Aug 2004 22:34:25 GMT, Jeremy Howard wrote:
>[color=green]
>>Hello everyone,
>>
>>I'm not a database guru so I'm sorry if this is a dumb question but here
>>it
>>goes...
>>
>>I have this sql query that I'm trying to run against a table on a Sql 2k
>>server:
>>
>> SELECT *
>> FROM ChangeTable
>> WHERE (CompanyName >= 'Ad%') AND (CompanyName <= 'L%')
>>
>>What I'm trying to do is get a range of data where the CompanyName column
>>is greater than or equal to and also less than or equal to certain values.
>>The problem I'm having is with the less than, equal to operator (<=); it
>>doesn't seem to evaluate to true when the CompanyName column is in an
>>equal
>>state. For example if I have this data:
>>
>> rowId CompanyName
>> 1 Advair
>> 2 Abus, Inc.
>> 3 Lohan Bros.
>> 4 Moop, Inc.
>>
>>Running the query from above I would expect (with my feeble database mind)
>>to get back two records (rowId 1 and 3) but I currently only getting back
>>one record (rowId 1).
>>
>>My question is this. Is the above query even valid in sql? Also can the
>>%
>>wildcard be used with the <= operator when dealing with varchar columns?
>>Thank you for any help with this issue.[/color]
>
> Hi Jeremy,
>
> The '%' can be used in <= comparisons, but it won't have the same meaning
> it has in a LIKE comparison. It will simply compare the ASCII value of
> whatever string is on the other side of the <= to the ASCII value of the %
> sign and return true or false.
>
> If you want all companies starting with Ad, all companies starting with L
> and all companies that are alphabetically ordered between them, try
>
> WHERE CompanyName >= 'Ad'
> AND CompanyName <= 'LZZZZZZZZZZZZZ ZZZZ'
>
> or
>
> WHERE CompanyName BETWEEN 'Ad' AND 'LZZZZZZZZZZ'
>
> or
>
> WHERE CompanyName >= 'Ad'
> AND CompanyName < 'M'
>
> (note I used less than, not less then or equal to - this also means I
> can't use the BETWEEN equivalent anymore)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)[/color]

[Erland Sommarskog]

Re: Less than, equal to Operator question

Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:[color=blue]
> The '%' can be used in <= comparisons, but it won't have the same meaning
> it has in a LIKE comparison. It will simply compare the ASCII value of
> whatever string is on the other side of the <= to the ASCII value of the %
> sign and return true or false.[/color]

It is worth pointing out that unless you are using a binary collation,
ASCII values does not matter much. I ran this statement on my machine:

SELECT char(chr), chr
FROM (SELECT DISTINCT chr = OrderID % 256
FROM Northwind..Orde rs) AS a
--WHERE chr BETWEEN 32 AND 255
ORDER BY char(chr)

In my collation, Finnish_Swedish _CS_AS, % does come together with another
bunch of punctuation characters, and they are mainly in ASCII order. But
for instance the + sign appears between characters 155 and 60.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Hugo Kornelis]

Re: Less than, equal to Operator question

On Fri, 13 Aug 2004 21:47:30 +0000 (UTC), Erland Sommarskog wrote:
[color=blue]
>Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:[color=green]
>> The '%' can be used in <= comparisons, but it won't have the same meaning
>> it has in a LIKE comparison. It will simply compare the ASCII value of
>> whatever string is on the other side of the <= to the ASCII value of the %
>> sign and return true or false.[/color]
>
>It is worth pointing out that unless you are using a binary collation,
>ASCII values does not matter much. I ran this statement on my machine:
>
> SELECT char(chr), chr
> FROM (SELECT DISTINCT chr = OrderID % 256
> FROM Northwind..Orde rs) AS a
> --WHERE chr BETWEEN 32 AND 255
> ORDER BY char(chr)
>
>In my collation, Finnish_Swedish _CS_AS, % does come together with another
>bunch of punctuation characters, and they are mainly in ASCII order. But
>for instance the + sign appears between characters 155 and 60.[/color]

Hi Erland,

Thanks for pointing that out! I am so used to the binary collation that I
sometimes forget that this is not the usual setting.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)