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.
4  9516 
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 <= '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)
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:41******** *************** *********@4ax.c om... 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 <= '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)
Hugo Kornelis (hugo@pe_NO_rFa ct.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..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, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
On Fri, 13 Aug 2004 21:47:30 +0000 (UTC), Erland Sommarskog wrote: Hugo Kornelis (hugo@pe_NO_rFa ct.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..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.
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) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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(). Otherwise I'm lost. Any suggestions
or examples would be greatly appreciated
Thanks,
Ridimz
|
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 to find dates in a given range, the
result set never includes records that have the ending date. For
example, if my SQL statement is
|
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 *
FROM ChangeTable
|
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 of the class. Now this is exactly what I need,
most of the time. I've found a couple of instances where I would like to be
able to sort (using the 'sort' algorithm) on a different attribute of my
class. Is there any other way I can take...
|
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
| | |
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.. (After reading that
|
by: Martin Jørgensen |
last post by:
Hi,
- - - - - - - - - - - - - - -
#include <iostream>
#include <string>
#include <map>
using namespace std;
int main()
{
|
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 compilers, I
guess).
|
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:
IIf(1=1,#3/12/2007#,#3/12/2007#)
1=1 will be replaced by actual 'test'; I just want to make sure
expression evaluates 'true'
Query returns all records where field equals
3/12/2007date
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
