SQL Replace last 2 chars if last 2 chars matches

Question

I hope someone can help me. I have a column with 160.000 rows. A lot of those column values (permalinks) end with "-2" which is unneccesary so I want to remove them. But I can't get it to work.

I tried it with the following query:

UPDATE wp_pods_cars
SET permalink = Replace(permalink,'-2','')
WHERE RIGHT( 'permalink' , 2 ) = '-2';

This query seems to be valid but the RIGHT() seems to make troubles. Probably it can just be used for a SELECT and not in a WHERE-clause.

I was thinking about Regex, but I didn't get that to work either. I already fail in finding the right regular expression for my case.

You should remove quotes in your `RIGHT` call, because 'permalink' is a literal string, not your column reference. — Oleg Komarov, May 23 '15 at 13:47

score 1 · Answer 1 · answered May 23 '15 at 14:02

1

You have single quotes around the column name, so you are comparing a constant string in the where clause. The version that comes closer to working is:

UPDATE wp_pods_cars
    SET permalink = Replace(permalink,'-2','')
    WHERE RIGHT(permalink, 2 ) = '-2';

However, I would write this as:

UPDATE wp_pods_cars
    SET permalink = LEFT(permalink, length(permalink) - 2) 
    WHERE permalink LIKE '%-2';

The -2 might appear at other places in the string and you don't want to remove all occurrences.

answered May 23 '15 at 14:02

Gordon Linoff

1,242,037
58
646
786

instead of `-2` how would I choose my own ending? such as ending with `101` – Mar 22 '19 at 14:09
@Eduards . . . You would either modify the `%-2` in the question or ask a new question if you are not clear on what needs to be done. – Gordon Linoff Mar 22 '19 at 14:30
just have @Gordon Linoff – Mar 22 '19 at 14:31

A l w a y s S u n n y · Answer 2 · 2015-05-24T07:08:09.403

First of all remove quotes from column name as per comment by Oleg .This may be another solution with your criteria, the MySQL SUBSTRING function extracts portion of a string, CHAR_LENGTH function calculates the number of characters in the string & finally assign it to permalink.

 UPDATE wp_pods_cars
  SET permalink = SUBSTRING(permalink, 1, CHAR_LENGTH(permalink) - 2) 
 WHERE permalink LIKE '%-2';

More About:

SUBSTRING

CHAR_LENGTH

SQL Replace last 2 chars if last 2 chars matches

2 Answers2

Linked