Filtering Users by Version Number with Regex in SQL

Most apps don't use simple integers as version numbers (e.g. version 1, version 2). Instead, they use alpha-numerics (e.g. v0.10.0).

Imagine you want to find all users on versions 0.9.10 through 0.10.5 (including all the alpha and beta builds). Maybe you want to message them all before a forced update.

Why SQL?

Selecting all these versions in a B.I. tool is a pain. You click 50 different versions in a drop-down. Hopefully you haven't missed any on accident and that your selection preferences save 🤞

Filtering users by version number in SQL is much faster. Plus, with regular expressions (or regex), you add a versatile and powerful tool in your data toolbox.

SQL you can copy / paste

select distinct
  id,
  app_version,
from users
where app_version ~* '(0\.9\.1\d)|(0\.10\.)'
;

Understanding the regex in this query

Let's break down the where clause:

~* is a regular expression operator. It’s like LIKE and similar to SIMILAR TO 😉 . It's case insensitive. See the Postgres docs.

We're searching through strings, hence the single quotes. The parentheses simply encompass your regex search terms. The vertical bar or pipe ( | ) functions like the OR operand.

\. tells regex: “the period should be a part of the search term.” Why can't you just use a period? Well, a period is also a special character in regex that means “any single character”. It's like a placeholder. The \ is an escape character, which means “I'm actually looking for a period, I'm not using that special period character.”

And lastly, \d is a placeholder for “any digit 0-9”. Think of it as 0.9.1X where X is any digit 0-9. This notation returns any version 0.9.12 or 0.9.13 or 0.9.14, etc.

Additional examples

To make sure you've got it, pretend you're searching for versions 0.9.10 through 0.11.19. The SQL changes every so slightly:

where e.app_version ~* '(0\.9\.1\d)|(0\.10\.)|(0\.11\.)' -- notice we added |(0\.11\.) to get all 0.11.X versions

Or say you're searching for versions 0.9.0 through 0.11.19 (but not 0.11.20):

where e.app_version ~* '(0\.9\.)|(0\.10\.)|(0\.11\.1\d)' -- see how \d gives us all 0.11.1X versions, but no 0.11.2X, etc?

Try it yourself?

Run this template against our sample database that mirrors real startup data. See the connection credentials, then connect in PopSQL.

Bonus Content

• Writing a regular expression can be tricky so we use Rubular to check before running queries (h/t @Lovitt 🙏 ).
• Despite the quirky name, the “world's most tyrannosaurical regex tutorial” is quite useful for learning Regex. Spoiler: it has very little to do with 🦖 ...
• Need to also filter by platform? Check out our SQL template.

Note: this template can be used by startups/projects of all levels of success: _{Credit to XKCD.}