I have the following query which works apart from when one of the values I need to search for has a single quote in the string. Its the only one like this and its breaking the query and i cant find a way to escape it that works.
SELECT r.CREATED_DATE, r.AD_ID,
(select value FROM PBANK.AD_FIELDS WHERE FIELD_ID ='Jobtitle' AND ad_id = r.AD_ID) as job_title,
(select value FROM PBANK.AD_FIELDS WHERE FIELD_ID ='Department' AND ad_id = r.AD_ID) as Department,
(select forename || ' ' || surname FROM PBANK.users WHERE USER_PK_ID = R.RAISER_ID) as Raiser,
(select status_name FROM PBANK.ATRSTATUS WHERE STATUS_ID = A.STATUS_ID) as status,
A.STATUS_ID
FROM PBANK.ATRREQUEST r
JOIN PBANK.ATRSTEP a on a.step_id = r.step_id AND a.ad_id = r.ad_id
LEFT JOIN PBANK.AD_FIELDS d on r.AD_ID = d.ad_id
WHERE acc_id='201050'
AND r.CREATED_DATE >= TO_DATE('2021-01-01', 'YYYY-MM-DD HH24:MI:SS')
AND r.CREATED_DATE <= TO_DATE('2021-04-30','YYYY-MM-DD HH24:MI:SS') AND A.STATUS_ID = 'Approved' AND d.field_id = 'Department' AND d.value IN ('Retail Management','1st Floor Retail', 'Ground Floor Retail','Bar 3'6');
As you can see Bar 3'6 is what is causing me the issue. Is there a way to esacape this 'Bar 3'6' in Oracle 9i?
