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:

giving…

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…

Original Data Example Integers

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

Between Example Integers

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

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…

Incorrect Between Example

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

How to use NOT BETWEEN in SQL Server

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.

Between Character Data in SQL Server Example

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

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…

And…

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…

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…

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

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