CODEWITHSUNDEEP
mysqlsqlregex
Jim
Jim

Reputation: 622

Remove matched portion of a string if it matches a pattern

I have a field "model" in my table Vehicles (which has just under 59,000 entries) which may have a value of something like:

Roadline (09-14)

I want to remove the (XX-XX) if it exists and fill in the "treated" value to a field modelname.

Any help will be greatly appreciated!

Upvotes: 0

Views: 128

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272006

A very simple solution is to use SUBSTRING_INDEX to isolate the portion before (. You can use REGEXP to make sure the pattern matches but, unfortunately, you cannot use it to capture matches.

SELECT
  model,
  CASE
    WHEN model REGEXP '\\([0-9]+-[0-9]+\\)$' THEN SUBSTRING_INDEX(model, '(', 1)
    ELSE model
  END AS modelname
FROM vehicles

Once you have made sure the data looks OK, you can update the other column like this:

UPDATE vehicles
SET modelname = CASE
  WHEN model REGEXP '\\([0-9]+-[0-9]+\\)$' THEN SUBSTRING_INDEX(model, '(', 1)
  ELSE model
END

Upvotes: 1

Related Questions