Re: can wild cards be used in BETWEEN clause

On Mon, 12 Apr 2004 10:58:58 -0400, "SAL" <2_at_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_at_andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk         / http://www.andyhsoftware.co.uk/space