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.
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
~* 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
\. 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.”
\d is a placeholder for “any digit 0-9”. Think of it as
X is any digit 0-9. This notation returns any version 0.9.12 or 0.9.13 or 0.9.14, etc.
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.
- 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.
Spread the wordTweet