在Oracle中具有文字字符(冒号，分号)的SELECT查询 [英] SELECT query with Literal characters(Colon, Semi-colon) in Oracle
本文介绍了在Oracle中具有文字字符(冒号，分号)的SELECT查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在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-All-06262019"'
数据:
Result
{"generated":"6/19/2019 12:54:55 PM -05:00","msgid":"SYSTEMID_115064221_2","servicenumber":"115064221","queueid":2,"expected":"7/1/2019 12:00:00 AM -05:00","startrange":"","endrange":"6/24/2019 12:00:00 AM -05:00","startdate":"6/24/2019 12:00:00 AM -05:00","flag":1,"setby":"6/5/2019 12:00:00 AM","location":"11","consideredby":"ATS","type":"FDA","generalinfo":"NA"}
{"generated":"6/26/2019 11:32:30 PM -05:00","msgid":"SYSTEMID_115064221_2","servicenumber":"115064221","queueid":2,"expected":"7/8/2019 12:00:00 AM -05:00","startrange":"","endrange":"7/1/2019 12:00:00 AM -05:00","startdate":"7/1/2019 12:00:00 AM -05:00","flag":0,"setby":"","location":"11","consideredby":"ATS","type":"FDA","generalinfo":"NA"}
{"generated":"6/25/2019 6:27:10 AM -05:00","msgid":"SYSTEMID_115064221_3","servicenumber":"115064221","queueid":3,"expected":"7/8/2019 12:00:00 AM -05:00","startrange":"","endrange":"6/24/2019 12:00:00 AM -05:00","startdate":"6/24/2019 12:00:00 AM -05:00","flag":1,"setby":"7/1/2019 12:00:00 AM","location":"11","consideredby":"ATS","type":"FDA","generalinfo":"NA"}
{"generated":"6/19/2019 12:54:56 PM -05:00","msgid":"SYSTEMID_115047234_2","servicenumber":"115047234","queueid":2,"expected":"7/1/2019 12:00:00 AM -05:00","startrange":"","endrange":"6/24/2019 12:00:00 AM -05:00","startdate":"6/24/2019 12:00:00 AM -05:00","flag":1,"setby":"6/5/2019 12:00:00 AM","location":"11","consideredby":"ATS","type":"FDA","generalinfo":"NA"}
{"generated":"6/25/2019 6:27:10 AM -05:00","msgid":"SYSTEMID_115047234_3","servicenumber":"115047234","queueid":3,"expected":"7/8/2019 12:00:00 AM -05:00","startrange":"","endrange":"7/1/2019 12:00:00 AM -05:00","startdate":"7/1/2019 12:00:00 AM -05:00","flag":1,"setby":"7/1/2019 12:00:00 AM","location":"11","consideredby":"ATS","type":"FDA","generalinfo":"NA"}S@{"msgid":"115047234_3","servicenumber":"115047234","queueid":"3","flag":0,"location":"11","generated":"6/26/2019 2:49:02 AM -05:00","type":"FDAAuthorize","generalinfo":"Authorize-All-06262019"}
{"generated":"6/19/2019 12:54:56 PM -05:00","msgid":"SYSTEMID_115035858_2","servicenumber":"115035858","queueid":2,"expected":"7/1/2019 12:00:00 AM -05:00","startrange":"","endrange":"6/24/2019 12:00:00 AM -05:00","startdate":"6/24/2019 12:00:00 AM -05:00","flag":1,"setby":"6/5/2019 12:00:00 AM","location":"11","consideredby":"ATS","type":"FDA","generalinfo":"NA"}
{"generated":"6/25/2019 6:27:10 AM -05:00","msgid":"SYSTEMID_115035858_3","servicenumber":"115035858","queueid":3,"expected":"7/8/2019 12:00:00 AM -05:00","startrange":"","endrange":"7/1/2019 12:00:00 AM -05:00","startdate":"7/1/2019 12:00:00 AM -05:00","flag":1,"setby":"7/1/2019 12:00:00 AM","location":"11","consideredby":"ATS","type":"FDA","generalinfo":"NA"}S@{"msgid":"115035858_3","servicenumber":"115035858","queueid":"3","flag":0,"location":"11","generated":"6/26/2019 1:19:08 AM -05:00","type":"FDAAuthorize","generalinfo":"Authorize-All-06262019"}S@{"msgid":"115035858_3","servicenumber":"115035858","queueid":"3","flag":0,"location":"11","generated":"6/26/2019 1:20:46 AM -05:00","type":"FDAAuthorize","generalinfo":"Authorize-All-06262019"}
{"generated":"6/19/2019 12:54:56 PM -05:00","msgid":"SYSTEMID_115036054_2","servicenumber":"115036054","queueid":2,"expected":"7/1/2019 12:00:00 AM -05:00","startrange":"","endrange":"6/24/2019 12:00:00 AM -05:00","startdate":"6/24/2019 12:00:00 AM -05:00","flag":1,"setby":"6/5/2019 12:00:00 AM","location":"11","consideredby":"ATS","type":"FDA","generalinfo":"NA"}
{"generated":"6/25/2019 6:27:10 AM -05:00","msgid":"SYSTEMID_115036054_3","servicenumber":"115036054","queueid":3,"expected":"7/8/2019 12:00:00 AM -05:00","startrange":"","endrange":"7/1/2019 12:00:00 AM -05:00","startdate":"7/1/2019 12:00:00 AM -05:00","flag":1,"setby":"7/1/2019 12:00:00 AM","location":"11","consideredby":"ATS","type":"FDA","generalinfo":"NA"}S@{"msgid":"115036054_3","servicenumber":"115036054","queueid":"3","flag":0,"location":"11","generated":"6/26/2019 1:38:55 AM -05:00","type":"FDAAuthorize","generalinfo":"Authorize-All-06262019"}
{"generated":"6/17/2019 7:50:06 AM -05:00","msgid":"SYSTEMID_116456178_2","servicenumber":"116456178","queueid":2,"expected":"6/24/2019 12:00:00 AM -05:00","startrange":"6/22/2019 12:00:00 AM -05:00","endrange":"6/18/2019 12:00:00 AM -05:00","startdate":"6/18/2019 12:00:00 AM -05:00","flag":1,"setby":"6/3/2019 12:00:00 AM","location":"11","consideredby":"ATS","type":"FDA","generalinfo":"NA"}
如何获取具有"generalinfo"值和"NA"值而不是"NA"行的行?
How to get the rows which has "generalinfo" having value and "NA" instead of only "NA" rows?
推荐答案
如果您的数据库版本为12c，只要您的列(result)的格式与json一致，则可以通过添加检查约束来轻松确定:
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:
alter table table1
add constraints chk_result_json
check(result is json);
并检查generalinfo不是NA那样:
and check generalinfo is not NA as :
select *
from table1 t
where t.result.generalinfo != 'NA'
通过将treat(result AS json)用作:
select *
from ( select id, treat(result AS json) as result from table1 ) t
where t.result.generalinfo != 'NA'
这篇关于在Oracle中具有文字字符(冒号，分号)的SELECT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
