Member Avatar for mpc123

Hi

I need to delete words before a set of words in a field.

eg delete the words before the word price in that field.

I already have a query to replace a certain word but cant see how adapt it to do what I require
update TABLE set field = replace(field,'price','');

can somebody help please?
Thanks in advance

  • 2 Contributors
  • 1 Reply
  • 203 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

mpc123 wanted to remove everything that appears before the first occurrence of the word "price". As noted, a simple position-based approach (LOCATE/POSITION + SUBSTRING) is the fastest portable option on older MySQL servers; it’s worth restricting updates with a WHERE so you don’t rewrite unchanged rows. (mysqltutorial.org)

If you run MySQL 8.x (or a modern MariaDB), use the built‑in regex replace. This example keeps the first occurrence of the word "price" and everything after it (case‑insensitive):

-- MySQL 8+
UPDATE mytable
SET myfield = REGEXP_REPLACE(myfield, '^[\s\S]*?(price)', '$1', 1, 0, 'i')
WHERE myfield REGEXP 'price';

In MariaDB the same idea works but replacement backreferences use \1 instead of $1; test with a SELECT before you UPDATE. (dev.mysql.com)

If you cannot upgrade (MySQL < 8.0), do the processing outside SQL (PHP is common). Example PHP preview/update flow:

$new = preg_replace('/^.*\bprice\b/i', 'price', $oldValue);
if ($new !== $oldValue) {
    // run a prepared UPDATE for this row
}

Pre‑8 MySQL really didn’t offer a built‑in regex-replace, so client-side or a UDF were the usual choices. Always preview changes with SELECT and test on a copy before running mass UPDATEs. (stackoverflow.com)

Notes and cautions: decide whether you want the first or last "price" (use non‑greedy .*? to target the first). Use \b (word boundary) if you only want the word itself. Escape backslashes as needed for your client/driver, and wrap updates in transactions or run them on a backup when possible.

Member Avatar for Member #120589

You'd probably need to do a regexp replacement, but MySQL doesn't have one. You could try this: https://github.com/hholzgra/mysql-udf-regexp

However you can use LOCATE() or POSITION() to find the position of a word and then you can trim the field using SUBSTRING():

Something like this...

UPDATE table1 SET field1 = IF(LOCATE('$TEXT',field1) > 0, SUBSTRING($field1, LOCATE('$TEXT', field1)), field1)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.