How I use REGEX to extract a portion of a string and add it to another column and then group by?

user_14834626 0
Viewed 66

I have used regex before in google sheets to add another column from something like:

PROP BAKERY (47) / PROPRIETARY BAKERY (60) / RESALE BREAD (950)

To Achieve this:

PROP BAKERY (47)

Using:

REGEXEXTRACT([range],"[^.*/]+"

How do perform this in mySQL so that I can: Create another column with the extracted text?

Any help would be greatly appreciated, thanks.

Update: I forgot to mention that the text can be variable, hence why a regular expression was needed in google sheets. Here are some other examples:

PROP BAKERY (47) / PROPRIETARY BAKERY (60) / RESALE BREAD (950) GROCERIES (5) / CLEANSING (52) / DISHWASHING DETERGENT (208) PRODUCE (30) / PRODUCE - VEGETABLE (101) / ORGANIC VEGETABLES (1043)

mysql
1 Answers

Use SUBSTRING_INDEX here:

SELECT SUBSTRING_INDEX(col, ' / ', 1)
FROM yourTable;

Demo

You could also use a regex replacement approach, and remove the pattern \s*/.*$:

SELECT REGEXP_REPLACE(col, '\\s*/.*$', '')
FROM yourTable;
user_18632290
Related