开发者

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,'"','\\"'),'"}')
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜