23

I want to make a search using "between" clause over a string column. Doing some test I got this:

Let's assume that there is a country table with a "name" column of type varchar. If I execute this query:

Select * from country where name between 'a' and 'b'

I got this result:

Argentina
.
.
.
Argelia.

It excludes those countries that starts with B which I found a little bit weird.

Is there a way to do this search in a more accurate way? Any other ideas for make this search?

Thanks in advance

Improve this question

6 Answers 6

Reset to default
56

The expression

name between 'A' and 'B'

is equivalent to

name>='A' and name<='B'

So 'Argentina' is >='A' and <='B' and it satisfies the condition. But 'Bolivia' is NOT <='B'. 'Bolivia'>'B'. It doesn't just look at the first letter: it looks at the whole string. Which is surely the way it ought to be: if it didn't do this, there'd be no way to say that you wanted a range that included 'Smith' but not 'Smithers'.

To accomplish what you want, you could say:

substr(name,1,1) between 'A' and 'B'

or:

name like 'A%' or name like 'B%'

or:

name>='A' and name<'C'
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Can also try BETWEEN 'a' AND 'bz' or BETWEEN 'a' AND 'c'
@AntonioEscorcia Those wouldn't work with some edge cases. "between 'a' and 'bz'" wouldn't includes "bza". "between 'a' and 'c'" would include "c" but not "ca". If your data was English words those letter combinations would be unlikely to come up, but if it was codes, it's possible.
What about LIKE '[ab]%' or LIKE '[a-b]%' (in case of needing the range)?
@AntonioEscorcia Yes, that should work. Depending on the DB engine, you might need to check "like '[ab]%' or like '[AB]%'". I've seen some that are case sensitive and some that are not. Easiest way to find out is to try it -- probably a lot faster than searching documentation.
2

i think i know how to solve your problem. u can try adding extra character in the back like this

select * from tablea where column1 between 'ABC' and 'ACD'+'Z'

this will return a result from ABC% to ACE

Improve this answer

Comments

1

The result's accurate, but you may be misunderstanding. x between a and b means x>=a and x<=b. (See the PostGRES documentation for details.)

If you want to get lines that start with either an a or a b, say what you mean:

select * from country where name like 'a%' or name like 'b%'

like uses the table indices so you'll get full performance for using this.

Improve this answer

5 Comments

Oh! well you were right on the understading part (and the whole answer as well). thanks
Actually "x between 'a' and 'b'" is equivalent to "x>='a' and x<='b'"
Not so... select 1 between 0 and 1; // true
RE "like uses the table indices": This wasn't true for older versions of Postgres. I think it was in 8.0 that they started using indices with likes. So if you have an old version, this could be an issue. Of course "between" and "> and <" also use indices, and have for as long as I've been using Postgres.
@Richard Umm, I think you got the variable names mixed up. Where did y come from? And "x between 'a' and 'b'" means that a>=x? I think you mean x>='a'. Etc.
0 
Select * from country where substring(name FROM 1 FOR 1) between 'A' and 'B';
Improve this answer

Comments

0

The reason this statement didn't work is SQL pads the string with whitespace until it's the same length as the comparing string. So, in comparison, sql compared b followed by several blanks with b******. Because whitespace appears before all the other letters, sql decided b***** came after b[6 spaces]. So it shouldn't be displayed.

Improve this answer

1 Comment

In this case, difference in white space is not the issue.
0

Another query that would get countries that start with b as well as A, would be:

Select * from country where name between 'a' and 'c'
Improve this answer

1 Comment

Though if you had a country named 'c', it would pass that test. Maybe not relevant in this example -- I'm pretty sure there's no country in the world named simply "C" -- but might come up in other examples.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.