New Topic

NoSQLBooster for MongoDB (formerly MongoBooster)Topic Question

How to escape a single quote in a string?

Sharon Snyder shared this question ago

Answered

I have a string in my database that has a ' character in the string. I'm searching for the record with this string in a field - and I can't escape the single quote character.

select *

from messages

where type='new record'

and payload.language='fr-FR'

and payload.normalization='aller au boulevard gouin ouest et l' avenue le mesurier montréal'

How can i escape the ' in l' avenue?

The same question

Replies (3)

qinghai (support@nosqlbooster.com) ●

The current build(4.5.2) does not provide such functionality, please download 4.5.3 and give it a try.

Win: https://s3.mongobooster.com/download/releasesv4/nosqlbooster4mongo-4.5.3.exe

Mac: https://nosqlbooster.com/s3/download/releasesv4/nosqlbooster4mongo-4.5.3.dmg

Linux: https://s3.mongobooster.com/download/releasesv4/nosqlbooster4mongo-4.5.3.AppImage

In the 4.5.3, you can use literal("aller au boulevard gouin ouest et l' avenue le mesurier montréal") to escape single quote char.

select *

from messages

where type='new record'

and payload.language='fr-FR'

and payload.normalization=literal("aller au boulevard gouin ouest et l' avenue le mesurier montréal")

Reply URL

Sharon Snyder ●

Thanks for the quick response! Unfortunately the literal doesn't seem to be translated correctly. Here is the aggregation that it generated using my example (look at the $project the $aller - although it did interpret the ' correctly)

db.messages.aggregate(

db.messages.aggregate(

Reply URL

qinghai (support@nosqlbooster.com) ●

Which platform are you using? Win, Mac or Ubuntu?

What's your MongoDB Server Version? 3.2, 3.4 or 3.6?

And, Could you give me your complete SQL statement? Is it a SQL statement like the following?

select *

from messages

where type='new record'

and payload.language='fr-FR'

and payload.normalization=literal("aller au boulevard gouin ouest et l' avenue le mesurier montréal")

Reply URL

Sharon Snyder ●

I'm using Windows. My Mongo server is actually 3.0 (linux) still (hopefully upgrading it soon).

That is the SQL statement (I replaced 'new record' with something else, but otherwise it is the same as you wrote above)

URL

qinghai (support@nosqlbooster.com) ●

A version of a dependency package for the previous 4.5.3 build was mistaken.. We have fixed the bug. Can you download and try again?

Win: https://s3.mongobooster.com/download/releasesv4/nosqlbooster4mongo-4.5.3.exe

URL

Sharon Snyder ●

Better - but it generates a query that doesn't return any records. I suspect that the query it's generating is wrong. Here's all the info

Here is the query I created (mongo) which should match the SQL query - and it returns a single record (like I expected it to)

db.messages.aggregate(

Better - but it generates a query that doesn't return any records. I suspect that the query it's generating is wrong. Here's all the info

Here is the query I created (mongo) which should match the SQL query - and it returns a single record (like I expected it to)

db.messages.aggregate(

URL

qinghai (support@nosqlbooster.com) ●

I found a simpler solution that does not require the use of literal, just use \\' to escape single quote '.

Please noted, \\', not \'

select *

from messages

where type='ANNOTATION_CORRECTED@1'

and payload.language='fr-FR'

and payload.normalization='aller au boulevard gouin ouest et l\\' avenue le mesurier montréal'

and timestamp > Date('2018-01-01')

URL

Sharon Snyder ●

That works perfectly! Thank you! Now the only issue is that the editor doesn't color the escaped single quote, but it also doesn't have an issue with it - and the query generated is exactly the one I created :)

Thanks!!!!

URL