An Update to Skipping the Leading Digit in T-SQL–#SQLNewBlogger

There was an interesting question in a forum, which I wrote about before. How do you skip the leading digit in a numeric value. I had looked at a UNION, but someone had a better suggestion, so I’m adding to the post.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Here are some hints to get started.

Another Predicate

In the previous post, I used a <> and then a > and < with a UNION. Howver, someone noted that NOT BETWEEN is an option. I could rewrite the query this way:

SELECT *
 FROM dbo.Room AS r
 WHERE room NOT BETWEEN  200 AND 299

This gives me the same result:

2022-10-05 14_32_43-SQLQuery6.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (61))_ - Microsoft SQL Server

This also results in a seek.

2022-10-05 14_33_30-SQLQuery6.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (61))_ - Microsoft SQL Server

The BETWEEN or NOT BETWEN is sargable, meaning we can use an index to find those values that fall into this ordering and exclude them.

Worth remembering BETWEEN when writing queries for ranges.

SQL New Blogger

An update to my post, showing a better way, showing I am happy to take advice from others, and I can improve code. This is a skill that employers need, especially when teams need to become more consistent with coding when working together. Showing you can adapt and grow is a good skill.

You could write a simple blog like this that shows how you might change some code you wrote in the past. I bet it takes less than 30 minutes.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

4 Responses to An Update to Skipping the Leading Digit in T-SQL–#SQLNewBlogger

  1. zikato says:

    There is a UNION ALL in the code sample. But only UNION in the query and plan screenshots.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.