How to remove pattern from a text field

[magicmarkuk]

Hi

I have a site built on Wordpress which has a over 4,000 posts all with the suffix -2 at the end of the post name (due to me forgetting to clear the trash) before running an import).

I would like to remove the -2 from the post names with a little bit of SQL so that for example my-example-post-2 becomes my-example-post and so on.

The table is wp-posts and the field is post_name.

I tried

update wp_posts set post_name = 
replace(post_name,'-2','');

but this didn't work, I suspect but not sure, it may be because it looking for an actual post name of '-2' rather than the equivalent of %-2

Any help really appreciated as although I have backed up my database and will do before running any SQL I don't want to realy mess it up be experimenting on my own further.

Thanks
Mark

[LastMitch]

I would like to remove the -2 from the post names with a little bit of SQL so that for example my-example-post-2 becomes my-example-post and so on.

You need to used a preg_match() function.

You can read more here:

http://php.net/manual/en/function.preg-match.php

Also take a look at this too (since you are using WordPress the link will explain how to remove strings text):

http://digwp.com/2010/03/remove-replace-content-wordpress-database/

[magicmarkuk]

Thank you.

After reading the Wordpress article you referred to

UPDATE wp_posts SET post_name = REPLACE ( post_name, '-2', '' );

the above worked!

Thanks
Mark