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;
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;
This gives us 2 and 3, so this seems to be inclusive, right?
SELECT StoredNumber FROM dbo.BetweenKeywordTestInt
WHERE StoredNumber BETWEEN 4 AND 4;
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;
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;
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;
OK, let’s use between and some characters now…
SELECT StoredText FROM dbo.BetweenKeywordTestVarchar
WHERE StoredText BETWEEN 'n' AND 'z';
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';
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';
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%';
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.