CODEWITHSUNDEEP
mysqlregexstring
WilliamLou
WilliamLou

Reputation: 1904

regex for mysql query?

say, I have a varchar(18) field(name:firmware) in a mysql database table.

The string format is like: ABC100-DE-3.10-0347, or it might looks like ABC100-DE-3.1-347. Basically, the only thing I can rely on is the "-" delimiter, the last part(0347,347) is called build number.

The 3.1/3.10 part is called version number. Is it possible to only select the build number and version number out from this field? I suspect this query might turn help to regex, since string functions won't do the job.

Thanks!

Upvotes: 0

Views: 474

Answers (3)

Richard JP Le Guen
Richard JP Le Guen

Reputation: 28753

The RegEx you're looking for it: ^(?:\w[\w\d]*\-)+(\d[\d\.]*)

Depending on where you use it you may need start and end delimiters: /^(?:\w[\w\d]*\-)+(\d[\d\.]*)/

The assumption is that the first time a delimiter (-) is followed by a decimal digit, that's your version number.

Not %100 sure about the MySQL, but something like...

SELECT * FROM `MyTable` WHERE `MyColumn` REGEXP '^(?:\w[\w\d]*\-)+(\d[\d\.]*)'

... might work.

Upvotes: 0

Cajunluke
Cajunluke

Reputation: 3113

Sure - try something like [^-]+-[^-]+-([0-9\.]+)-([0-9]+), where the first capturing group is your version number and the second is the build number.

To be specific, the first group looks for a sequence of numbers and periods and the second looks for a sequence of numbers.

Upvotes: 0

Fosco
Fosco

Reputation: 38526

Give this a shot:

select SUBSTRING_INDEX(theField,'-',-1) as buildNumber,
       SUBSTRING_INDEX(SUBSTRING_INDEX(theField,'-',-2),'-',1) as versionNumber
from theTable

Upvotes: 2

Related Questions