In this post we will discuss BETWEEN operator in SQL server. it is used for pattern searching.
- To select data that is within a range of values, the BETWEEN operator is used. The BETWEEN operator allows the selection of rows that contain values within a specified lower and upper limit.
- The lower value must be coded first. The two values of range must be linked with the keyword AND.
- The BETWEEN operator can be used with both character and numeric data types.
- We can use the BETWEEN operator in where clause with select, update, Delete.
- We can also rewrite BETWEEN operator using less than and equal (<=) or grater than and equal (>=).
- If we pass NULL values to the BETWEEN operator it returns NULL value in the result.
- It’s return type is Boolean.
column BETWEEN lower_value AND upper_value;
- We can also combine the BETWEEN operator with the NOT operator whose column values are not in the specified range.
Retrieve the name of product whose price is between 50 and 200.
select pname from tblproduct where price BETWEEN 50 AND 200;
or we can also write
select pname from tblproduct where price>=50 AND price<=200;
Retrieve the productname, price, manufacturing date between 1/1/2017 And 31/12/2017.
select pname, price, manufadate from tblproduct where manufadate BETWEEN ‘1/1/2017′ AND ’12/31/2017’;
Retrieve the pid, pname where pid between 2 and 4.
select pid, pname from tblproduct where pid BETWEEN 2 AND 4;
Retrieve the name of product whose price is not between 50 and 200.
select pname, price from tblproduct where price NOT BETWEEN 50 AND 200;
You can also by SQL 70-761 book write by me from Amazon.