MySQL remove final part of a string after specific character

Question

I need to remove the last part of a string in a column where I have a field named "path" that looks like:

images/prop/images/2034/22399_2034.JPG

I need everything after the last "/" to be deleted, in order to have

images/prop/images/2034/

instead of

images/prop/images/2034/22399_2034.JPG

I have no idea if this is possible. Thanks.

score 14 · Answer 1 · answered Aug 04 '13 at 23:29

14

You can combine MySQL's TRIM() and SUBSTRING_INDEX() functions:

SELECT TRIM(TRAILING SUBSTRING_INDEX(path, '/', -1) FROM path)
FROM   my_table

See it on sqlfiddle.

answered Aug 04 '13 at 23:29

eggyal

can you tell me the syntax in order to update the whole table? – ol30cean0 Aug 05 '13 at 00:29
I am trying to update the whole data after select but I cant find the right code, help... – ol30cean0 Aug 05 '13 at 00:53
1

@ol30cean0: `UPDATE my_table SET path = TRIM(TRAILING SUBSTRING_INDEX(path, '/', -1) FROM path)`. – eggyal Aug 05 '13 at 07:18

1 Answers1