mysql specific substring

Question

I have a about 500.000 rows in database and they are look like:

":hello:hi:"
":bye:good bye:bye-bye:"
":hi:hi-hi:hi-hi-hi:hallo:"

So, i need to update my table and remove everything after third character ':' in value, so my result should be:

":hello:hi:"
":bye:good bye:"
":hi:hi-hi:"

Is it possible? Thx.

MySQL 4.1.22.

rxmnnxfpvg · Accepted Answer · 2011-04-13 20:08:11Z

3

Yes, the function SUBSTRING_INDEX does this:

UPDATE `table` SET `field` = CONCAT(SUBSTRING_INDEX(`field`, ':', 3), ':');

answered Apr 13, 2011 at 20:08

rxmnnxfpvg

31.2k50 gold badges129 silver badges190 bronze badges

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

1 Answer 1