How to escape a single quote in a string?

Sharon Snyder shared this question 2 years 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?

Comments (3)

photo
1

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")

photo
1

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(
  [{
      "$project": {
        "*": "$*",
        "type": "$type",
        "payload.language": "$payload.language",
        "payload.normalization": "$payload.normalization",
        "aller au boulevard gouin ouest et l' avenue le mesurier montréal": "$aller au boulevard gouin ouest et l' avenue le mesurier montréal",
        "__tmp_cond_1": {
          "$and": [{
              "$and": [{
                  "$eq": [
                    "$type",
                    "ANNOTATION_CORRECTED@1"
                  ]
                },
                {
                  "$eq": [
                    "$payload.language",
                    "fr-FR"
                  ]
                }
              ]
            },
            {
              "$eq": [
                "$payload.normalization",
                {
                  "$literal": "aller au boulevard gouin ouest et l' avenue le mesurier montréal"
                }
              ]
            }
          ]
        }
      }
    },
    {
      "$match": {
        "__tmp_cond_1": true
      }
    }
  ])

photo
1

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")

photo
1

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)

photo
1

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

photo
1

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(
  [{
      "$match": {
          "type": "ANNOTATION_CORRECTED@1", 
          "payload.annotationLanguage": 'fr-FR', 
          "timestamp": { "$gt": ISODate("2018-01-01T00:00:00.000Z") },
          "payload.normalization": "aller au boulevard gouin ouest et l' avenue le mesurier montréal"
      }
    }
  ]);


Using the following SQL:

select *
from messages
where type='ANNOTATION_CORRECTED@1'
and payload.language='fr-FR'
and payload.normalization=literal("aller au boulevard gouin ouest et l' avenue le mesurier montréal")
and timestamp > Date('2018-01-01')


I get the following generated mongo which does not return any records (and takes a long time to run - so it's doing something):

db.messages.aggregate(
  [{
      "$project": {
        "__tmp_cond_1": {
          "$and": [{
              "$and": [{
                  "$and": [{
                      "$eq": [
                        "$type",
                        "ANNOTATION_CORRECTED@1"
                      ]
                    },
                    {
                      "$eq": [
                        "$payload.language",
                        "fr-FR"
                      ]
                    }
                  ]
                },
                {
                  "$eq": [
                    "$payload.normalization",
                    {
                      "$literal": "aller au boulevard gouin ouest et l' avenue le mesurier montréal"
                    }
                  ]
                }
              ]
            },
            {
              "$gt": [
                "$timestamp",
                ISODate("2018-01-01T00:00:00.000Z")
              ]
            }
          ]
        },
        "__doc": "$$ROOT"
      }
    },
    {
      "$match": {
        "__tmp_cond_1": true
      }
    }
  ])

photo
2

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')

photo
1

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!!!!

photo