Version number(x.y.z) comparison in SQL query by using regular expression

Question

I have a SQL database table like below.

id	version	data
1	1.0.1	xxx
2	1.1.0	yyy
3	1.1.3	zzz
4	1.10.1	aaa
5	2.0.0	bbb

I would like to select the data which version is greater than or equal to 1.1.0.

In above case, the expected output is below.

id	version	data
2	1.1.0	yyy
3	1.1.3	zzz
4	1.10.1	aaa
5	2.0.0	bbb

Would you tell me how to extract that in SQL query ??

Assuming you are talking about MySQL, [this StackOverflow link](https://stackoverflow.com/questions/7508313/mysql-sorting-of-version-numbers) has an accepted answer. — tajacks, Dec 22 '21 at 02:01
MSSQL 2016 and higher has [STRING_SPLIT](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15) — paulsm4, Dec 22 '21 at 02:19
Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Dec 22 '21 at 06:10
If you are using Postgres, you can use `WHERE string_to_array(version, '.')::int[] >= array[1,1,0]` — , Dec 22 '21 at 06:12

paulsm4 · Answer 1 · 2021-12-22T02:31:36.333

If your "version string" had nice, fixed-length fields (e.g. "01.10.02") then it's easy.

Since yours doesn't, MSSQL 2016 and higher offers STRING_SPLIT.

Alternatively, this is a great solution that you should be able to use with most RDBMs (including Oracle, MSSLQ and mySql):

https://stackoverflow.com/a/33560117/421195

SQL> WITH DATA AS
  2    ( SELECT 'F/P/O' str FROM dual
  3    )
  4  SELECT SUBSTR(str, 1, Instr(str, '/', -1, 1) -1) part1,
  5         SUBSTR(str, Instr(str, '/', -1, 1) +1) part2
  6  FROM DATA
  7  /

PART1 PART2
----- -----
F/P   O

The example breaks the string into separate columns (e.g. major/middle/minor numbers).

From there, you need to compare the corresponding columns in different rows.

Conceivably, you can do everything in one SQL "select". In practice, it would probably be easier to write a stored procedure to do one piece at a time.

I hope that helps point you in the right direction...

Version number(x.y.z) comparison in SQL query by using regular expression

1 Answers1