Mysql concat with regexp

Question

please advise following sql:

I wish to replace text in a field where value similar "Priority: 1" with "Priority: 2". However text to find could be "Priority: 1" OR "Priority: 2" OR "Priority: 3" etc. Was thinking of using regexp along with concat to find any value that matches the pattern

An example of complete data in field:

 <span class='badge badge-info'>Priority: 1</span><br>&bull; Lead submitted: <br>&bull; Existing customer: <br>&bull; Car of interest:

In this instance I would like to replace "Priority: 1" with "Priority: 2"

SQL so far...

UPDATE
  response
SET
  notes = REPLACE(
    notes,
    CONCAT('Priority: ', REGEXP '\d'),
    'Priority: 3'
  )
WHERE actionid = '2198602'

Answer 1

You mentioned in a comment above that you are using MySQL 5.x, so the REGEXP_REPLACE() function is not available.

This means you have a few options:

Replace fixed strings, one at a time.

UPDATE
  response
SET
  notes = REPLACE(
    notes,
    'Priority: 1',
    'Priority: 3'
  )
WHERE actionid = '2198602';

Then do a similar update for 'Priority: 2' and another update for any other value you need to change.

Replace fixed strings, in a nested fashion.

The result of one REPLACE() is a string, which you can use as the first argument to another REPLACE(). The string will be checked for each input pattern, and the replacement made.

UPDATE
  response
SET
  notes = REPLACE(
    REPLACE(
      notes,
      'Priority: 1',
      'Priority: 3'
    ),
    'Priority: 2',
    'Priority: 3'
  )
WHERE actionid = '2198602';

Fetch the value into a client application

Select the value, fetch it, then you can use regular expressions to make string substitutions. Virtually every language has support for regular expressions in some manner.

Upgrade to MySQL 8.0

Obvious option. Then you can use REGEXP_REPLACE().

Note that MySQL 5.7 will reach end-of-life in October 2023, less than one year away as we write this. You should already be thinking about upgrading to 8.0.