It would be nice to be able to use NOCASE with WHERE as well as with ORDER BY

e.g.
SELECT MyField1, MyField2 FROM MyTable WHERE MyField1 = 'abc' AND MyField2 LIKE 'x%'
NOCASE ORDER BY MyField1, MyField2 NOCASE;

SteveW

What's wrong with UPPER?

SELECT MyField1, MyField2 FROM MyTable WHERE MyField1 = 'abc' AND UPPER(MyField2) LIKE UPPER('x%')
ORDER BY MyField1, MyField2 NOCASE;

Bit more typing but works a treat and is easy to spot

Roy Lambert

<<What's wrong with UPPER?>>

You need 2 UPPERs for 1 NOCASE

SteveW

>You need 2 UPPERs for 1 NOCASE

And I thought I was lazy

Roy Lambert

SteveW wrote:
> <<What's wrong with UPPER?>>
>
> You need 2 UPPERs for 1 NOCASE

So? In Delphi, I need a begin/end for a {} in C/C++/Java/younameit. Type it
once, change it 10 times, read it a thousand times. Typing time is
_unimportant_.

Ralf

<<So? In Delphi, I need a begin/end for a {} in C/C++/Java/younameit. Type it
once, change it 10 times, read it a thousand times. Typing time is
_unimportant_.>>


Why have a combine harvester when you could cut the wheat with a scythe?
Laziness is the mother of invention.

It also has a nice consistency about it, I can use NOCASE with CREATE INDEX and ORDER BY
so why not with WHERE?

SteveW wrote:
....
> Why have a combine harvester when you could cut the wheat with a
> scythe?
> Laziness is the mother of invention.

For me, a double UPPER is way more descriptive than a NOCASE somewhere at
the end. Make code readable is more important than reducing the keystrokes.

> It also has a nice consistency about it, I can use NOCASE with CREATE
> INDEX and ORDER BY so why not with WHERE?

Because UPPER is standard. More or less every SQL db system use that thing.
And because it is clear. You can write WHERE Field1=Field2, WHERE
UPPER(Field1) = Fíeld2, WHERE Field1=UPPER(Field2) and WHERE UPPER(Field1) =
UPPER(Field2). They all have different meanings. And why do I need two
different notations for one function?

Ralf

<<For me, a double UPPER is way more descriptive>>
Then carry on using it, I'm not suggesting doing away with it.

<<than a NOCASE somewhere at the end. >>
It wouldn't necessarily be at the end, you're not getting the idea...

<<You can write WHERE Field1=Field2, WHERE
UPPER(Field1) = Fíeld2, WHERE Field1=UPPER(Field2) and WHERE UPPER(Field1) =
UPPER(Field2)>>

Or you could write: WHERE Field1=Field2, WHERE
UPPER(Field1) = Fíeld2, WHERE Field1=UPPER(Field2) and WHERE Field1 =
Field2 NOCASE

<<And why do I need two different notations for one function?>>
There are things you need and things you desire, one of my desires is to use NOCASE with WHERE, ok...