SQL select statement where the text I am looking for has single quotes

Question

I am trying to do a select statement in Microsoft SQL server management studio. The data looks like this:

My select statement is:

SELECT * FROM OSUSR_W7I_INVENTORYCHANGELOG 
WHERE OBJECTTYPEID = 3
and CREATEDBY is null
and TEXT = 'State changed from ''Awaiting Prep'' to ''Import Error'''

But this brings back no results, despite there being lots of records with this text.

I was able to retrieve some data by using this statement:

SELECT * FROM OSUSR_W7I_INVENTORYCHANGELOG 
WHERE OBJECTTYPEID = 3
and CREATEDBY is null
and TEXT like '%Awaiting Prep%'
and TEXT like '%Import Error%'

but unfortunately it brings back too many results because it is bringing back data that states: 'State changed from 'Import Error' to 'Awaiting Prep'' where I am only looking for 'State changed from 'Awaiting Prep' to 'Import Error''

Please can anyone help. From reading other posts putting a double quote in should solve the issue of the single quotes but in this instance it doesn't work. Many thanks

Answer 1

You can combine your LIKE to one using % as placeholders, instead of:

SELECT * FROM OSUSR_W7I_INVENTORYCHANGELOG 
WHERE OBJECTTYPEID = 3
and CREATEDBY is null
and TEXT like '%Awaiting Prep%'
and TEXT like '%Import Error%'

do:

SELECT * FROM OSUSR_W7I_INVENTORYCHANGELOG 
WHERE OBJECTTYPEID = 3
and CREATEDBY is null
and TEXT like '%Awaiting Prep%Import Error%'

Answer 2

You can try using LTRIM and RTRIM in sql server. If there is case of some white space. Otherwise your statement is seems ok

SELECT * FROM OSUSR_W7I_INVENTORYCHANGELOG 
WHERE OBJECTTYPEID = 3
and CREATEDBY is null
and LTRIM(RTRIM(TEXT)) = 'State changed from ''Awaiting Prep'' to ''Import 
Error'''