regex for mysql query?

Question

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!

Fosco · Accepted Answer · 2011-06-10 18:14:18Z

Give this a shot:

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

cajunluke · Accepted Answer · 2011-06-10 18:15:26Z

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.

Richard JP Le Guen · Accepted Answer · 2011-06-10 18:18:05Z

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.

Collectives™ on Stack Overflow

regex for mysql query?

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related