I have a MySQL table "products" with a descriptions field which contains the word "pack" or "pack of":

Headphones 3 pack, Built-in Microphone - (Latest Model)
Wireless headphones -Bluetooth Headphones 3.5 -3 Pack
Flash Drive-128G-B35 -(12pack)
Wireless headphones -Bluetooth Headphones -(11- pack) Latest Model
Wireless headphones -Bluetooth Headphones -(Pack of 11)
Wireless headphones -Bluetooth Headphones -(packs of 11)

I need a regular expression to extract the number before word "pack" and after words "pack of" ,

The output should be :

3
3
12
11
11
11

Tried a lot of combinations for the regular expression

    SELECT @str:="Headphones 3 pack, Built-in Microphone - (Latest Model)" AS str,
                   regexp_replace(@str, '[^0-9]*(pack)', '') 0 AS packof;

This one works for number in front of words "pack of" still not sure if it covers all scenarios:

SELECT @str:="Wireless headphones -Bluetooth Headphones -(Pack of 11)" AS str,
               regexp_replace(@str, '.*pack[^0-9]*', '') 0 AS packof;

CodePudding user response：

You can use

regexp_replace(@str, '(?i)^.*?(?:packs?(?:\\W of)?\\W*(\\d )|(\\d )\\W*pack).*', '$1$2')

See the regex demo. Details:

• (?i) - case insensitive matching on
• ^ - start of string
• .*? - any zero or more chars other than line break chars, as few as possible
• (?:packs?(?:\W of)?\W*(\d )|(\d )\W*pack) - either of:
  • packs?(?:\W of)?\W*(\d ) - pack, an optional s, an optional sequence of one or more non-word chars and then of word, zero or more non-word chars, and one or more digits captured into Group 1
  • | - or
  • (\d )\W*pack - one or more digits captured into Group 2, zero or more non-word chars, and then a pack string
• .* - any zero or more chars other than line break chars, as many as possible.

The $1$2 replacement replaces the match with Group 1 Group 2 values.