Select Query With Literal Characters(colon, Semi-colon) In Oracle

How can I query for data having literal chars as colon, semi-colon and some part of text in Oracle? SELECT result FROM TABLE1 WHERE result like ''generalinfo'':''Authorize-Al

Solution 1:

If your DB version is 12c, then you can easily figure out by adding a check constraint provided your column (result)'s format conforms with json as:

altertable table1 
addconstraints chk_result_json  
check(resultis json);

and check generalinfo is not NA as :

select * 
  from table1 t
 where t.result.generalinfo != 'NA'

Even easier for 18c version by using with treat(result AS json) as :

select*from ( select id, treat(resultAS json) asresultfrom table1 ) t
 where t.result.generalinfo !='NA'

Demo