MySQL Escape double quotes in query result
I have a CONCATENATION problem regarding quotes. In my database I have single and double quoted text and then I buld a JSON string with CONCAT,
CONCAT('{"',a,'":"',b,'"}')
Lets say we have the following data:
a b
Phrase Monica's mirror
Phrase Joe "Hammer" Smith
Phrase Oo-la-laaa
The concatenation will be
{"Phrase":"Monica's mirror"}
{"Phrase":"Joe "Hammer" Smith"}
{"Phrase":"Oo-la-laaa"}
As you can see 'Joes "Hammer" Smith' will create an invalid json string.
QUESTION
Is there a way in SQL t开发者_运维百科o escape quotes (in the CONCAT)? so I get this result:
{"Phrase":"Monica's mirror"}
{"Phrase":"Joe \"Hammer\" Smith"}
{"Phrase":"Oo-la-laaa"}
Remember, this is not on the PHP side, it needs to be done in the SQL query,
Thank you...Have you tried something like this?
CONCAT('{"',REPLACE(a,'"','\\"'),'":"',REPLACE(b,'"','\\"'),'"}')
精彩评论