CODEWITHSUNDEEP
mysqlsql
BeoWulf
BeoWulf

Reputation: 657

Replace portion of string in many records with a single query

I have a MySQL table with a varchar filed that has many records like:

folder/subfolder_1/file_xpto
folder/subfolder_2/file_abc
folder/subfolder_3/file_123
folder/subfolder_4/file_xyz

I would like in a single query to remove the portion of the string "/subfolder_x" so in the end it will be:

folder/file_xpto
folder/file_abc
folder/file_123
folder/file_xyz

How can I achieve this?

Upvotes: 0

Views: 23

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use substring_index to get the first and last substrings and concatenate them using concat_ws.

select concat_ws('/',substring_index(colname,'/',1),substring_index(colname,'/',-1))
from tablename
where colname like 'folder/%'

Upvotes: 2

Related Questions