Unable to process a code with special character

Question

UPDATE `ps_product_lang` 
SET 
`description` = REPLACE(`description`, '<li><p>', '<li>')

Above code is not processing and says 0 Rows affected, however, the following code is working with some rows affected:

UPDATE `ps_product_lang` 
SET 
`description` = REPLACE(`description`, 'lip', 'li')

How do I process the previous code? I want to replace exactly <li><p>

I've tested your query and it seems to be fine. Could it be that your table doesn't really have that `'
I had the same problem in my php app, I just add to remove the strip_tags() function in my model. — Maxime, Apr 10 '19 at 01:57

score 1 · Accepted Answer · answered Apr 10 '19 at 01:47

1

You're question is similar to this. You may take a look at it:

Update a column value, replacing part of a string

I think you may want to add the LIKE and WHERE clause in your UPDATE query. Something like this: UPDATE yourtable SET url = REPLACE(url, 'http://domain1.com/images/', 'http://domain2.com/otherfolder/') WHERE url LIKE ('http://domain1.com/images/%');

answered Apr 10 '19 at 01:47

aries

64
1
12

I dont think WHERE and LIKE are needed as I want to process it for all the rows. – Arpan Jain Apr 10 '19 at 02:30
did you try to look up on the link I gave you and tried it? – aries Apr 10 '19 at 02:43

score 0 · Answer 2 · answered Apr 10 '19 at 01:55

0

Please ensure the description value doesn't be encoded by any encoding.

Or you can try to replace < with < and replace > with >

answered Apr 10 '19 at 01:55

samboy

31
1
4

the description is not encoded by any encoding & replacing < with < and replace > with > doesn't work either as the strings need to be searched. – Arpan Jain Apr 10 '19 at 02:29
Where did you executed the SQL? In a console or in a program? – samboy Apr 10 '19 at 05:33

score 0 · Answer 3 · answered Apr 10 '19 at 01:59

0

Below query working fine for me.

UPDATE s_demo SET description = REPLACE(description,'<li><p>','<b>222<b>') WHERE id = 1

I think one thing need to check description field Collation type. In my case i used Collation = utf8_general_ci
I hope, this is to help you

answered Apr 10 '19 at 01:59

Proxion Group

70
3

Collation = utf8_general_ci for the table. The query is not working. – Arpan Jain Apr 10 '19 at 02:27
Sorry it's my mistake, with other collation type working fine for me. see in below screen shot. https://www.screencast.com/t/0cmWQxkwO – Proxion Group Apr 10 '19 at 02:50

Unable to process a code with special character

3 Answers3