CODEWITHSUNDEEP
mysql
Christopher Penn
Christopher Penn

Reputation: 539

Trimming MySQL string after a given string

I'm drawing a blank on this MySQL command. I have a set of strings like so in a variable named pagetitle in a table called scanner:

Marketing analytics via Christopher Penn

And I want to lop off everything after, including the via, to yield:

Marketing analytics

I did this:

UPDATE scanner SET pagetitle = SELECT TRIM(TRAILING ' via ' FROM pagetitle) FROM scanner

Which according to the manual should do what I'm looking for, but it's still yielding the original string.

What am I doing wrong?

Upvotes: 0

Views: 24

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

The TRAILING option with TRIM refers to trailing characters, not entire words begun with a certain word. Try using INSTR here:

UPDATE scanner
SET pagetitle = LEFT(pagetitle, INSTR(pagetitle, 'via') - 2);
WHERE pagetitle REGEXP '[[:<:]]via[[:>:]]';

Demo

If you are using MySQL 8+ or later, then we can try using REGEXP_REPLACE:

UPDATE scanner
SET pagetitle = REGEXP_REPLACE(pagetitle, '\\s+via.*', '');

Upvotes: 2

Related Questions