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

Question

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)

I couldn't help to think that there's more to the question than just "getting the first value in a column where cell data are separated by slashes". But if it is just getting the first value, the [SUBSTRING_INDEX](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring-index) answer from Tim should be already enough. — FanoFN, Feb 01 '21 at 08:35

score 0 · Accepted Answer · answered Feb 01 '21 at 08:23

0

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;

answered Feb 01 '21 at 08:23

Tim Biegeleisen

502,043
27
286
360

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

1 Answers1

Demo