CODEWITHSUNDEEP
mysqlsqlregexdatabasedatabase-administration
Cheruiyot Felix
Cheruiyot Felix

Reputation: 1597

Find and replace part of a string in MySQL

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  
 );

Upvotes: 0

Views: 182

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

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 '%/%/%';

Upvotes: 1

Related Questions