473,320 Members | 2,161 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Less than, equal to Operator question

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.
Jul 20 '05 #1
4 9480
On Thu, 12 Aug 2004 22:34:25 GMT, Jeremy Howard wrote:
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.


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 <= 'LZZZZZZZZZZZZZZZZZ'

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)
Jul 20 '05 #2
Hugo,

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

Jeremy

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:41********************************@4ax.com...
On Thu, 12 Aug 2004 22:34:25 GMT, Jeremy Howard wrote:
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.


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 <= 'LZZZZZZZZZZZZZZZZZ'

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)

Jul 20 '05 #3
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
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.


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..Orders) 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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
On Fri, 13 Aug 2004 21:47:30 +0000 (UTC), Erland Sommarskog wrote:
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
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.


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..Orders) 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.


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)
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Ridimz | last post by:
I have implemented a binary search tree and am interested in displaying all keys less than a certain value. I understand how to approach this task if the searchValue is equal to root.key()....
1
by: Propel Exacto | last post by:
Hey guys, I am using MySQL 4.0.18 and I have a field named "order_datetime" in which I store data in the format 20041001 23:00:00 (for example Oct 1, 2004 11pm) When I do a select statement...
0
by: Jeremy Howard | last post by:
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 ...
2
by: deancoo | last post by:
Yet another question... I have a container (vector say) containing my class objects. I've overloaded the less than operator in my class definition to allow sorting based on a certain attribute...
15
by: Murt | last post by:
Hi, when writing equations in vb .net, how do you enter the signs "less than or equal to" etc. thanks Murt
161
by: KraftDiner | last post by:
I was under the assumption that everything in python was a refrence... so if I code this: lst = for i in lst: if i==2: i = 4 print lst I though the contents of lst would be modified.....
11
by: Martin Jørgensen | last post by:
Hi, - - - - - - - - - - - - - - - #include <iostream> #include <string> #include <map> using namespace std; int main() {
16
by: Cory Nelson | last post by:
Does anyone know how std::set prevents duplicates using only std::less? I've tried looking through a couple of the STL implementations and their code is pretty unreadable (to allow for different...
3
by: sfrvn | last post by:
I have searched high and low and cannot find an answer to my problem. So now I turn to the collective genius of this newsgroup. Over-simplified examples This query criteria for field works:...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.