SOQL having single quotes (') in the label value on PermissionSet record is not getting queried using the LIKE operator on the label field

Question

Created a Permission set name : John's ps

Query to be run : SELECT Id, Label FROM PermissionSet where label like 'John\'s ps'

Result : [object Object]: MALFORMED_QUERY: CustomPermissionSet WHERE Label LIKE 'John's ps' ^ ERROR at Row:1:Column:64 unexpected token: 's'

Similar Queries tried that works fine :

Query 1 : SELECT Id, label FROM PermissionSet WHERE Label = 'John\'s BBQ' , it gives proper result.

Query 2 : SELECT Id, label FROM PermissionSet WHERE Label like 'John%' , it gives proper result.

Any insights on why this is happening why LIKE when used with (') with a escape sequence is not working ?

Answer 1

The issue you are encountering is due to the single quote in the permission set name. In SOQL, single quotes need to be escaped by using a backslash (\).

SOQL: SELECT Id, Label FROM PermissionSet WHERE Label LIKE 'John\'s ps'

Update:

I have tried with the same name in the permission set and got the same error. As per the documentation, To use the single quote(') in the SELECT query we need to use the backslash \ character to escape.

I have also tried the same name in the account object with the below query and it will work as expected. But I thing it is an issue with the PermissionSet Object.

SOQL: SELECT Id, Name FROM Account WHERE Name LIKE 'John\'s ps%'

Output:

I suggest that create a case with Salesforce.