How to use the Between Keyword in SQL Server T-SQL: SQL Developer Series

Hello and here we have some demos showing how to use Between in SQL Server. The whole script is at the bottom of the post.

In our Sequallity training classes, I’m always banging on about keeping the amount of text down. It just makes it easier for the brain to understand what’s going on with the T-SQL trickery we are looking at.

So I really like the Between keyword as it reduces the text in a SQL statement. Although it’s not a CASE statement, it simply displays whatever evaluates to true between the two supplied expressions.

For this example, I’m using the SQL Azure Database:


SELECT @@version

giving…


Microsoft SQL Azure (RTM) - 12.0.2000.8 
	Feb 26 2020 10:26:43 
	Copyright (C) 2019 Microsoft Corporation

Simple Between Example

OK, so notice the drop table if exists keyword, great if you’re the only one using the table, not so good for many users though…


DROP TABLE IF EXISTS dbo.BetweenKeywordTestInt;

CREATE TABLE dbo.BetweenKeywordTestInt 
(
StoredNumber INT
)

INSERT INTO dbo.BetweenKeywordTestInt VALUES (1);
INSERT INTO dbo.BetweenKeywordTestInt VALUES (2);
INSERT INTO dbo.BetweenKeywordTestInt VALUES (3);
INSERT INTO dbo.BetweenKeywordTestInt VALUES (4);
INSERT INTO dbo.BetweenKeywordTestInt VALUES (5);

SELECT StoredNumber FROM dbo.BetweenKeywordTestInt;
Original Data Example Integers

So five values in the table, but how does Between work on these integers?


SELECT StoredNumber FROM dbo.BetweenKeywordTestInt 
WHERE StoredNumber BETWEEN 2 AND 3;
Between Example Integers

This gives us 2 and 3, so this seems to be inclusive, right?


SELECT StoredNumber FROM dbo.BetweenKeywordTestInt 
WHERE StoredNumber BETWEEN 4 AND 4;
Similar Between in SQL Server Example

OK, we can repeat the same number in the from and to expression…so what if we do it all wrong? It has happened before and it will happen again…


SELECT StoredNumber FROM dbo.BetweenKeywordTestInt 
WHERE StoredNumber BETWEEN 5 AND 3;
Incorrect Between Example

Eek! Now I’ve tried to be too clever…

How to use NOT BETWEEN in SQL Server


SELECT StoredNumber FROM dbo.BetweenKeywordTestInt 
WHERE StoredNumber NOT BETWEEN 2 AND 3;
How to use Not Between in SQL Server Example Output

Now that’s brought back everything not between 2 and 3, ok, makes sense. Ok, so that’s numbers covered but wait, what about…

How to use Between and Like together in SQL Server

What I love about Microsoft’s SQL Server is that I learn all the time, and playing, teaching and working with SQL, teaches the intracacies of how it actually works. OK, so let’s flip that integer over to a varchar.


DROP TABLE IF EXISTS dbo.BetweenKeywordTestVarchar;

CREATE TABLE dbo.BetweenKeywordTestVarchar 
(
StoredText VARCHAR(255)
)

INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('Hello,');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('my');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('name');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('is');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('SQLJohn');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('And');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('I');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('know');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('T-SQL');

SELECT StoredText FROM dbo.BetweenKeywordTestVarchar;
Between Character Data in SQL Server Example

OK, let’s use between and some characters now…


SELECT StoredText FROM dbo.BetweenKeywordTestVarchar 
WHERE StoredText BETWEEN 'n' AND 'z';
Between Example Two Expressions

OK, so the query brought back everything between n and z. That’s cool. OK, so let’s put in some more data…


INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('Nzzz');

And…


SELECT StoredText FROM dbo.BetweenKeywordTestVarchar
WHERE storedtext BETWEEN 'Nz%' AND 'Z';
Between And Like in SQL Server Example Output

OK so the percent wildcard seems to work in the between keyword. Let’s now put in Nz only…


INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('Nz');

SELECT StoredText FROM dbo.BetweenKeywordTestVarchar 
        WHERE storedtext BETWEEN 'Nz%' AND 'Z';
Missing Between Data in SQL Server Example Output

Notice how the query is not bringing back the new Nz when I think it should and an essential rule of a good DBA/Developer, always check your work…


SELECT StoredText FROM dbo.BetweenKeywordTestVarchar
	WHERE storedtext LIKE 'Nz%';
Between Data Final Check Output

And yes, it does exist in the table so the Wildcard does not work in Between.

Between Conclusion

A really nice keyword and is inclusive but you can use LIKE but not with a wildcard, which kind of makes sense with some simple demos of how to use Between in SQL Server T-SQL.

Full Between Demo Script


SELECT @@version

-- Between and Integers
DROP TABLE IF EXISTS dbo.BetweenKeywordTestInt;

CREATE TABLE dbo.BetweenKeywordTestInt 
(
StoredNumber INT
)

INSERT INTO dbo.BetweenKeywordTestInt VALUES (1);
INSERT INTO dbo.BetweenKeywordTestInt VALUES (2);
INSERT INTO dbo.BetweenKeywordTestInt VALUES (3);
INSERT INTO dbo.BetweenKeywordTestInt VALUES (4);
INSERT INTO dbo.BetweenKeywordTestInt VALUES (5);

SELECT StoredNumber FROM dbo.BetweenKeywordTestInt;

SELECT StoredNumber FROM dbo.BetweenKeywordTestInt 
WHERE StoredNumber BETWEEN 2 AND 3;

SELECT StoredNumber FROM dbo.BetweenKeywordTestInt 
WHERE StoredNumber BETWEEN 4 AND 4;

SELECT StoredNumber FROM dbo.BetweenKeywordTestInt 
WHERE StoredNumber BETWEEN 5 AND 3;

--NOT Between
SELECT StoredNumber FROM dbo.BetweenKeywordTestInt 
WHERE StoredNumber NOT BETWEEN 2 AND 3;


-- Between and Characters
DROP TABLE IF EXISTS dbo.BetweenKeywordTestVarchar;

CREATE TABLE dbo.BetweenKeywordTestVarchar 
(
StoredText VARCHAR(255)
)

INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('Hello,');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('my');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('name');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('is');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('SQLJohn');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('And');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('I');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('know');
INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('T-SQL');

SELECT StoredText FROM dbo.BetweenKeywordTestVarchar;

SELECT StoredText FROM dbo.BetweenKeywordTestVarchar 
WHERE StoredText BETWEEN 'n' AND 'z';

INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('Nzzz');

SELECT StoredText FROM dbo.BetweenKeywordTestVarchar 
WHERE storedtext BETWEEN 'Nz%' AND 'Z';

INSERT INTO dbo.BetweenKeywordTestVarchar VALUES ('Nz');

SELECT StoredText FROM dbo.BetweenKeywordTestVarchar
WHERE storedtext BETWEEN 'Nz%' AND 'Z';

SELECT StoredText FROM dbo.BetweenKeywordTestVarchar
WHERE storedtext LIKE 'Nz%';

I will do a future post on SQL Server datetimes and how interesting/esoteric they are.

Please check out our warranty page.

Leave a Reply