regexp_substr a numeric ratio within a string

Question

How can I leverage the regexp_substr function (in redshift) to extract numeric ratios from a string? Examples:

Product name 1:4 subtype name
10:5 product name
[5:1] product name
product name [1:15]

Would like to capture just the 1:4 portion of the text.

Thanks

Answer 1

You could use regexp_substr() like so:

regexp_substr(mycol, '[0-9]+:[0-9]+')

This extracts the (first) match of a pattern made if at least one digit, followed by a semi-colon, then at least one digit.