Geek asks and answers

MySql will replace what after the last /

I am trying to replace the text after the last /in the path column with the text from the name column. The number /in the path column is not fixed, but in all cases I will only need to replace the text after the last/

What is the right way to do this? I am not very good at things like this and tried something funny that cleared all the data:

UPDATE paths
    SET path = SUBSTRING_INDEX(name,'/', 1);
    select * from paths;

Current data

"name"      "path"
"microsoft" "/us/software/mscorporation"

Expected results

"name"      "path" 
"microsoft" "/us/software/microsoft" 
//replace what after the last `/` with what in name
+3
source share
3 answers

just wanted to improve vooD's answer.

UPDATE paths SET path = REPLACE(path, SUBSTRING_INDEX(path, '/', -1), name);

My comment is not enough for comments: P

EDIT: The request above has an error, example

us/system/driver/system

'path will become us/microsoft/driver/microsoft if name value is microsoft

so, this should have the correct request:

UPDATE paths SET path = CONCAT(LEFT(path, CHAR_LENGTH(path) - CHAR_LENGTH(SUBSTRING_INDEX(path, '/', -1))),name)
+2
source

To select a substring after the last /, you can do:

select substring_index('/us/software/microsoft', '/', -1);
+--------------------------------------------------+
| substring_index('/us/software/microsoft','/',-1) |
+--------------------------------------------------+
| microsoft                                        |
+--------------------------------------------------+

UPDATE :

UPDATE paths SET path = SUBSTRING(path, 1, LENGTH(path) - LOCATE('/', REVERSE(path)) )
  substring_index(name, '/', -1);
+1

. / microsoft

UPDATE path SET path = REPLACE(path, SUBSTRING_INDEX(name, '/', -1), 'microsoft');
+1

More articles:


All Articles