MySQL string quotation for REPLACE()

Question

I have problem using REPLACE() function on specific data. It doesn't match string occurrence that it should replace.

The string I want to replace is the following.

s:54:"Doctrine\Common\Collections\ArrayCollection_elements

It is stored in the following field

`definitions` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:object)',

Here is the LIKE request which matches all rows that contain the string (notice \0 on the string):

SELECT `definitions` 
FROM `entity_type` 
WHERE `definitions` LIKE '%s:54:"\0Doctrine\\\\Common\\\\Collections\\\\ArrayCollection\0_elements%'

At the same time when I run the following request I get '0 rows affected' message and nothing is replaced:

UPDATE `entity_type`
    SET `definitions` = REPLACE(
        `definitions`, 
        's:54:"\0Doctrine\\\\Common\\\\Collections\\\\ArrayCollection\0_elements', 
        's:53:"\0Doctrine\\\\Common\\\\Collections\\\\ArrayCollection\0elements'
    );

How should I modify the string to make REPLACE() match the text I need and replace it?

PS: Please don't blame me for what I'm trying to replace. it is not my fault :-)

Answer 1

If your "where condition" works, you can try with:

UPDATE `entity_type`
    SET `definitions` = REPLACE(REPLACE(
        `definitions`, 
        's:54:', 
        's:53:'),'ArrayCollection_elements','ArrayCollectionelements')
where `definitions` LIKE '%s:54:"\0Doctrine\\\\Common\\\\Collections\\\\ArrayCollection\0_elements%';