CODEWITHSUNDEEP
mysqlreplace
EOB
EOB

Reputation: 3085

Replacing using wildcards or regex in MySQL?

I have values like this in my column: /1/0/101.00_1234.jpg

Now I want to replace the /1/0 with something else. Problem is, it can differ from row to row. It can be /h/a as well. How could I do that without any additional tools?

Thanks

Upvotes: 1

Views: 2723

Answers (3)

Alex Monthy
Alex Monthy

Reputation: 1877

Write a stored function which searches for the rightmost "/" in the field content and deletes all characters before that position. Then use this stored function to update the field. Of is this already an "additional tool" -? In this case use an inline function like (not tested)

RIGHT(fieldname,LENGTH(fieldname) - LOCATE('/', reverse(fieldname))

Upvotes: 1

Derek Tomes
Derek Tomes

Reputation: 4007

I'd try the SUBSTRING_INDEX key word:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index

for example:

SUBSTRING_INDEX(myfield, '/', -3)

Upvotes: 2

zerkms
zerkms

Reputation: 254916

Most likely you just need to replace first 4 chars, so use SUBSTRING and CONCAT functions:

CONCAT('/a/b', SUBSTRING(colunm_name, 4))

Upvotes: 2

Related Questions