SEO

Hi,
I am trying to select names of all countries from a column:
SELECT country FROM countries_table
WHERE
country BETWEEN 'A%' AND 'Z%';

This gives me every name except those starting with Z. I wonder if this is
the right approach.
Any help will be appreciated.

Thanks.

On Mon, 12 Apr 2004 10:58:58 -0400, SAL <2@2.com> wrote:

> Hi,
> I am trying to select names of all countries from a column:
> SELECT country FROM countries_table
> WHERE
> country BETWEEN 'A%' AND 'Z%';
>
> This gives me every name except those starting with Z. I wonder if this
> is
> the right approach.
> Any help will be appreciated.


It seems to me perfectly logical. Show me everything between A and B
should not show me B itself, because I've asked for things *between* two
points, but not the points themselves.

Of course, AZERBAIJAN would get into my list, because it's bigger than A,
and so is still between A and B.

If you want to see Zimbabwe and Zanzibar, then your 'between' markers need
to be A at one end, and something like 'ZZ' at the other. ZZ% would permit
anything up to ZY... to be displayed.

Regards
HJR
--
-------------------------------------------
Dizwell Informatics: http://www.dizwell.com
-A mine of useful Oracle information-
-Windows Laptop Rac-
-Oracle Installations on Linux-
===========================================

On Tue, 13 Apr 2004 08:18:38 +1000, Howard J. Rogers <hjr@dizwell.com>
wrote:

> On Mon, 12 Apr 2004 10:58:58 -0400, SAL <2@2.com> wrote:
>
>> Hi,
>> I am trying to select names of all countries from a column:
>> SELECT country FROM countries_table
>> WHERE
>> country BETWEEN 'A%' AND 'Z%';
>>
>> This gives me every name except those starting with Z. I wonder if this
>> is
>> the right approach.
>> Any help will be appreciated.
>
>
> It seems to me perfectly logical. Show me everything between A and B
> should not show me B itself, because I've asked for things *between* two
> points, but not the points themselves.
>
> Of course, AZERBAIJAN would get into my list, because it's bigger than
> A, and so is still between A and B.
>
> If you want to see Zimbabwe and Zanzibar, then your 'between' markers
> need to be A at one end, and something like 'ZZ' at the other. ZZ% would
> permit anything up to ZY... to be displayed.
>
> Regards
> HJR


Sorry... I meant to prove the point.

SQL> select * from countries where col1 between 'A%' and 'Z%';

COL1
--------------------
ALGERIA
BOTSWANA
CHAD
DALMATIA
ENGLAND
WESTPHALIA
YUGOSLAVIA

7 rows selected.

SQL> select * from countries where col1 between 'A%' and 'ZZ%';

COL1
--------------------
ALGERIA
BOTSWANA
CHAD
DALMATIA
ENGLAND
WESTPHALIA
YUGOSLAVIA
ZANZIBAR

8 rows selected.


--
-------------------------------------------
Dizwell Informatics: http://www.dizwell.com
-A mine of useful Oracle information-
-Windows Laptop Rac-
-Oracle Installations on Linux-
===========================================

On Mon, 12 Apr 2004 10:58:58 -0400, "SAL" <2@2.com> wrote:

>I am trying to select names of all countries from a column:
>SELECT country FROM countries_table
>WHERE
>country BETWEEN 'A%' AND 'Z%';

If you're trying to select 'names of all countries' why have a WHERE clause at
all?

>This gives me every name except those starting with Z. I wonder if this is
>the right approach.
>Any help will be appreciated.

BETWEEN doesn't accept wildcards, so that query probably doesn't do what you
want. You're relying on how '%' sorts in a string - in most character sets it's
less than any alphanumeric character. So it'd skip anything with a name
consisting of a single character 'A', and include a single character 'Z', but
exclude anything past that.

Sounds like you what you may want is:

WHERE SUBSTR(country, 1, 1) BETWEEN 'A' and 'Z'

(Is case an issue as well?)

--
Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

On Mon, 12 Apr 2004 23:27:42 +0100, Andy Hassall <andy@andyh.co.uk> wrote:

>
> BETWEEN doesn't accept wildcards,

So how come this works?

SQL> select * from countries where col1 between 'A%' and 'ZZ%';

COL1
--------------------
ALGERIA
BOTSWANA
CHAD
DALMATIA
ENGLAND
WESTPHALIA
YUGOSLAVIA
ZANZIBAR

And works, moreover, in 8.1.6, 9.2 and 10g.

Regards
HJR
--
-------------------------------------------
Dizwell Informatics: http://www.dizwell.com
-A mine of useful Oracle information-
-Windows Laptop Rac-
-Oracle Installations on Linux-
===========================================

Hello Roger,

----- Original Message -----
From: "Howard J. Rogers" <hjr@dizwell.com>
Newsgroups: comp.databases.oracle.server
Sent: Monday, April 12, 2004 7:17 PM
Subject: Re: can wild cards be used in BETWEEN clause


> On Mon, 12 Apr 2004 23:27:42 +0100, Andy Hassall <andy@andyh.co.uk> wrote:
>
> >
> > BETWEEN doesn't accept wildcards,
>
> So how come this works?


You are quite right about BETWEEN accepting 'wildcards'. However, the
following statement is incorrect:

<quote>
It seems to me perfectly logical. Show me everything between A and B
should not show me B itself, because I've asked for things *between* two
points, but not the points themselves.
</quote>

The between predicate implies a closed, not open interval, i.e. an
interval that includes its boundaries as well. You can easily verify this
with numeric data which are probably easier to understand than string data.
As for the example above, it works as it should.

Consider a set of strings like this {'a', 'ab', 'kl', 'z', 'za'}.

The following predicate, BETWEEN 'a%' and 'z%' will select {'ab', 'kl'}
because 'a' < 'a%' so it's not included in the subset; however 'z' < 'z%'
and IS included ( but 'za' > 'z%' and it's skipped). This predicate,
BETWEEN 'a' and 'z', naturally will select {'a', 'ab', 'kl', 'z'}.

VC

On Tue, 13 Apr 2004 00:43:42 GMT, VC <boston103@hotmail.com> wrote:

> Hello Roger,

I appear to get this all the time. The name is HOWARD, not roger.

"Rogers", with an 's', is my surname.

> You are quite right about BETWEEN accepting 'wildcards'. However, the
> following statement is incorrect:
>
> <quote>
> It seems to me perfectly logical. Show me everything between A and B
> should not show me B itself, because I've asked for things *between* two
> points, but not the points themselves.
> </quote>

The dangers of trying to simplify a message.

The point I should have made is that if you ask for things between 'A' and
'B', you want things starting at A-nothing-nothing-nothing..., which stop
at (and yes, include) B-nothing-nothing-nothing.... But 'BELGIUM' is
greater than 'B' on its own, and therefore isn't between the two points.
So it doesn't get included.

So yes, I agree with you. I phrased it very badly, and picked an extremely
silly example to illustrate the point.

Now, could you (and everyone else who makes the same mistake) try and get
my name right?

:-)
HJR
--
-------------------------------------------
Dizwell Informatics: http://www.dizwell.com
-A mine of useful Oracle information-
-Windows Laptop Rac-
-Oracle Installations on Linux-
===========================================

Hello Howard,

Please accept my apologies. I do know your first name very well -- I just
got distracted whilst typing my message because I was talking to someone
called Roger, can you believe that ?

Regards.

VC

"Howard J. Rogers" <hjr@dizwell.com> wrote in message
newspr6de8ny03d8uqx@news.optusnet.com.au...
> On Tue, 13 Apr 2004 00:43:42 GMT, VC <boston103@hotmail.com> wrote:
>
> > Hello Roger,
>
> I appear to get this all the time. The name is HOWARD, not roger.
>
> "Rogers", with an 's', is my surname.

Howard J. Rogers wrote:
> On Tue, 13 Apr 2004 00:43:42 GMT, VC <boston103@hotmail.com> wrote:
>
>> Hello Roger,
>
>
> I appear to get this all the time. The name is HOWARD, not roger.
>
> "Rogers", with an 's', is my surname.

<snip>

> Now, could you (and everyone else who makes the same mistake) try and
> get my name right?
>
> :-)

Roger, Howard, Over and Out.


Sorry, couldn't resist. McCallans' out early this week :-)

On Tue, 13 Apr 2004 02:22:56 GMT, VC <boston103@hotmail.com> wrote:

> Hello Howard,
>
> Please accept my apologies.

No problems. It's just I've had a spate of it lately. I think it's a
cultural thing... a lot of the people who do it are from Singapore and
such like places. I think I do it in reverse as well (ie, call them by
their first name when I should refer to them by their surname, otherwise
[I think] I appear desperately rude to them), but I'm not entirely sure.

It gets confusing and rather like walking on egg shells.

Regards
HJR
--
-------------------------------------------
Dizwell Informatics: http://www.dizwell.com
-A mine of useful Oracle information-
-Windows Laptop Rac-
-Oracle Installations on Linux-
===========================================