Remove all text after a certain word

Question

I have data in a database, the rows are like:

Ticket #3532 Updated
Ticket #43252 Opened
Ticket #5462 Opened
Ticket #353 Updated

on each row, there is text after the word Updated or Opened

I would like to remove this text on every row so it just displays exactly like the above.

I have tried using:

strstr($result["notes"], 'Updated', true)

For the Updated rows, but this returns

Ticket #34564 

and not the word updated

How can i execute this in either PHP or directly in SQL?

Answer 1

You can do the update directly in SQL using the LOCATE() function to find the first instance of Updated or Opened in the string, and the LEFT() string function to left-truncate it to that position plus the length of the word Updated or Opened. There would be a number of ways to handle this with string functions, but this is the first that comes to mind.

UPDATE tickets
SET notes = LEFT(notes, LOCATE('Opened', notes) + LENGTH('Opened'))
WHERE notes LIKE '%Opened%';

UPDATE tickets
SET notes = LEFT(notes, LOCATE('Updated', notes) + LENGTH('Updated'))
WHERE notes LIKE '%Updated%';

Both LEFT() and LOCATE() are documented among the MySQL string functions.

Here is an example (as a SELECT)

And the same example reformulated for the UPDATE statements