Menu
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.
Is there a way in SQL to 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...
876
Use two single quotes to escape them in the sql statement. The double quotes should not be a problem: SELECT 'How is my son''s school helping him learn? "Not as good as Stack Overflow would!"'
MySQL recognizes the following escape sequences. \0 An ASCII NUL (0x00) character. \' A single quote (“'”) character. \" A double quote (“"”) character.
Use braces to escape a string of characters or symbols. Everything within a set of braces in considered part of the escape sequence. When you use braces to escape a single character, the escaped character becomes a separate token in the query. Use the backslash character to escape a single character or symbol.
Have you tried something like this?
CONCAT('{"',REPLACE(a,'"','\\"'),'":"',REPLACE(b,'"','\\"'),'"}')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
