Between operator not working correctly

Between operator not working correctly

Post by Trav » Wed, 10 Jul 2002 22:52:58



Hello All,

I have been trying to get this where cause to work correctly.  

[LastName] >= 'A' AND [LastName] <= 'C'

Unfortunately, it only returns all records that have a last name with
A or B as the first letter.  I want it to also return records that
have C as first letter of the last name.  Does anyone have any
suggestions on how to get this to work?

Thanks,

Travis

 
 
 

Between operator not working correctly

Post by Keith Kratochvi » Wed, 10 Jul 2002 23:05:29


BETWEEN is working correctly.....the sql that you are using is not correct.

Observe this example:

USE pubs
GO
SELECT * FROM authors
WHERE SUBSTRING(au_lname,1,1) BETWEEN 'a' AND 'd'

--
Keith, SQL Server MVP


Quote:> Hello All,

> I have been trying to get this where cause to work correctly.

> [LastName] >= 'A' AND [LastName] <= 'C'

> Unfortunately, it only returns all records that have a last name with
> A or B as the first letter.  I want it to also return records that
> have C as first letter of the last name.  Does anyone have any
> suggestions on how to get this to work?

> Thanks,

> Travis


 
 
 

Between operator not working correctly

Post by BP Margoli » Thu, 11 Jul 2002 03:43:41


Travis,

[LastName] >= 'A' AND [LastName] < 'D'

'Cooper' is greater than 'C', but less than 'D'

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Hello All,

> I have been trying to get this where cause to work correctly.

> [LastName] >= 'A' AND [LastName] <= 'C'

> Unfortunately, it only returns all records that have a last name with
> A or B as the first letter.  I want it to also return records that
> have C as first letter of the last name.  Does anyone have any
> suggestions on how to get this to work?

> Thanks,

> Travis

 
 
 

Between operator not working correctly

Post by BP Margoli » Thu, 11 Jul 2002 03:44:53


Keith,

In the absence of Umachandar, I'll take on his usual task of pointing out
that the code you offered is unable to make use of an index on the au_lname
column, if one exists   :-)

BPM



> BETWEEN is working correctly.....the sql that you are using is not
correct.

> Observe this example:

> USE pubs
> GO
> SELECT * FROM authors
> WHERE SUBSTRING(au_lname,1,1) BETWEEN 'a' AND 'd'

> --
> Keith, SQL Server MVP



> > Hello All,

> > I have been trying to get this where cause to work correctly.

> > [LastName] >= 'A' AND [LastName] <= 'C'

> > Unfortunately, it only returns all records that have a last name with
> > A or B as the first letter.  I want it to also return records that
> > have C as first letter of the last name.  Does anyone have any
> > suggestions on how to get this to work?

> > Thanks,

> > Travis

 
 
 

Between operator not working correctly

Post by Greg Linwoo » Thu, 11 Jul 2002 09:58:41


I do agree with you BP, but it's a pity SQL Server considers this statement
non-SARGable, because there is no logical reason why it can't create a SARG
in this case.

[substring(au_lname, 1, n) = ...] should be useful to any index that leads
with au_lname. Obviously, anything other than 1 for the substring start is a
different story though..

Cheers,
Greg Linwood


> Keith,

> In the absence of Umachandar, I'll take on his usual task of pointing out
> that the code you offered is unable to make use of an index on the
au_lname
> column, if one exists   :-)

> BPM



> > BETWEEN is working correctly.....the sql that you are using is not
> correct.

> > Observe this example:

> > USE pubs
> > GO
> > SELECT * FROM authors
> > WHERE SUBSTRING(au_lname,1,1) BETWEEN 'a' AND 'd'

> > --
> > Keith, SQL Server MVP



> > > Hello All,

> > > I have been trying to get this where cause to work correctly.

> > > [LastName] >= 'A' AND [LastName] <= 'C'

> > > Unfortunately, it only returns all records that have a last name with
> > > A or B as the first letter.  I want it to also return records that
> > > have C as first letter of the last name.  Does anyone have any
> > > suggestions on how to get this to work?

> > > Thanks,

> > > Travis

 
 
 

Between operator not working correctly

Post by Keith Kratochvi » Thu, 11 Jul 2002 15:47:51


Agreed, but the code does pull the necessary data.  Thanks.

--
Keith, SQL Server MVP


> Keith,

> In the absence of Umachandar, I'll take on his usual task of pointing out
> that the code you offered is unable to make use of an index on the
au_lname
> column, if one exists   :-)

> BPM



> > BETWEEN is working correctly.....the sql that you are using is not
> correct.

> > Observe this example:

> > USE pubs
> > GO
> > SELECT * FROM authors
> > WHERE SUBSTRING(au_lname,1,1) BETWEEN 'a' AND 'd'

> > --
> > Keith, SQL Server MVP



> > > Hello All,

> > > I have been trying to get this where cause to work correctly.

> > > [LastName] >= 'A' AND [LastName] <= 'C'

> > > Unfortunately, it only returns all records that have a last name with
> > > A or B as the first letter.  I want it to also return records that
> > > have C as first letter of the last name.  Does anyone have any
> > > suggestions on how to get this to work?

> > > Thanks,

> > > Travis

 
 
 

1. Like operator not behaving correctly

Hi !

I am trying to use 'like' operator in the if statement using the wildcard '%'.
But it seems that it is not working properly.

The statement :

if paray.parameter like param_select.parameter then
   ...
   ...

Let's say the value of param_select.parameter is 'K%'. Then above statement
will be true only for the values 'K', 'KI', 'KC' etc of paray.parameter. In
other words it does not become true for the other true values like 'KIX',
'KILO', 'KIJU' etc. i.e. the statement is doing a wildcard checking
wherever the value is two character long.

If the value of param_select is '%' then the statement is doing a wildcard
checking wherever the value of paray.parameter is one character long.

Accoring to the Ingres/4GL Reference manual wildcard '%' should match any
character, regardless of length.

Does anyone have any experience with this ?

Thanks for any help.

Regards

G.D.Madan
-------------------------------------------------------------------------------
SGS-Thomson Microelectronics,           | Phone    : 4821411 Ext 335
28 Ang Mo Kio, Industrial Park II,      | Fax      : (65) 4820240          

-------------------------------------------------------------------------------

2. Using sting processing in Stored Procedures

3. SELECT...WHERE EmployeeID NOT IN (...) not working correctly

4. msdb restore

5. DBCC SHOWCONTIG not working correctly?

6. Who beats the speed of dbase IV 2.0 (on PC) ?

7. Help Please -- ADODB.Command does not work correctly in .NET

8. List of registry keys created by SQL Server 2000 install?

9. BUG: SQL SERVER 2k SET FMTONLY not working correctly on a db restored from v70 backup

10. OUTER JOINs Not Working Correctly

11. left outer joins with a whereclause seem to not work correctly

12. Linking to SQL db not working correctly

13. dts export to fixed field not working correctly or so it seems