Find and replace part of a string in MySQL

Question

I have a field called slug in my table with values in this format:

"username/campaign-name"

I would like to remove the values "username/" and remain with only "campaign-name".

I tried the following but stuck on how I can dynamically replace the string "hello"? Any other alternative method is appreciated.

UPDATE `campaigns`
 SET `slug` = REPLACE(`slug`, `slug`,'hello') 
   WHERE `slug` IN (  
    SELECT `slug`
    FROM (
        SELECT `slug`
        FROM `campaigns` 
        LIMIT 1
    ) slug  
 );

Gordon Linoff · Accepted Answer · 2014-07-11 14:49:00Z

1

I would suggest using substring_index():

UPDATE `campaigns`
     SET `slug` = substring_index(slug, '/', -1);

I'm not sure what the where clause is supposed to do. If you only want to change one row, use limit:

UPDATE `campaigns`
     SET `slug` = substring_index(slug, '/', -1)
     LIMIT 1;

For safety, you might want to verify the format of slug:

UPDATE `campaigns`
     SET `slug` = substring_index(slug, '/', -1)
     WHERE slug like '%/%' and slug not like '%/%/%';

answered Jul 11, 2014 at 14:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cheruiyot Felix Over a year ago

It works perfect well. And thanks for the last portion. I am able to validate and don't loop over everything. Thanks again

Collectives™ on Stack Overflow

Find and replace part of a string in MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related