sql - How to deal with semantic version data type in BigQuery

Question

I know that there is no data type in BigQuery. What would you prefer to deal with semantic versions in BigQuery?

I have the following schema:

software:string,
software_version:string

software_version column is string but the data I store there is in semver format: `MAJOR.MINOR.PATCH-prerelease

I especially want to perform operators < > =.

select '4.0.0' < '4.0.0-beta' 

This returns true, but according to the semver definition it's false. Because the char - is used for prerelease.

Answer 1

Waitting for answers