Search code examples
sqlsql-serversql-likebetween

Between operator

create table timepass
(
    first varchar(50)
);

insert into timepass values('ABCD');
insert into timepass values('A');
insert into timepass values('Dx');
insert into timepass values('D');

SELECT first
FROM timepass
WHERE first BETWEEN 'A%' AND 'D%';

I read that the output of between operator includes the values which is mentioned in between clause (inclusive).

Here the output is 

ABCD
D

Here A is not included but D is included. I am not able to understand this behavior. Can someone please explain this ?

Here is the sql fiddle link :- http://sqlfiddle.com/#!9/bf5cd5/1/0

Solution

  • you are not using like so '%' is treated as a regular character, you are retrieving strings greater than 'A%' up to 'D%'

    'A' < 'A%'

    make an insert of 'A%' and try again

    maybe this will bring your desired results

    SELECT first
FROM timepass
WHERE first between 'A' and 'D'