排除CLOB字段中具有特定文本的查询结果

Question

我希望在NRS中排除带有'富文本'字样的记录。FULL_TEXT (CLOB数据类型)字段。我在oracle sql工作。

我尝试使用NOT LIKE函数，但没有收到任何结果。此外，我尝试了以下代码，但收到了一个错误:ORA-00932:不一致的数据类型:期望-得到CLOB

SELECT 
PT.PATIENT_MRN AS MRN
,ORD.PATIENT_ID
,ORD.ORDER_DATE
,ORD.ORDER_TYPE
,ORD.ORDER_PROC
,ORD.SPECIMEN_SOURCE
,ORS.COMPONENT_NAME
,ORS.RESULT_TEXT
,NRS.FULL_TEXT
FROM RDM.PATIENT                PT
LEFT JOIN RDM.ORDERS            ORD ON PT.PATIENT_ID = ORD.PATIENT_ID 
LEFT JOIN RDM.ORDER_RESULT      ORS ON ORS.ORDER_ID = ORD.ORDER_ID
LEFT JOIN RDM.NOTE_RSLT         NRS ON ORD.ORDER_ID = NRS.ORDER_ID
WHERE ORD.ORDER_DATE BETWEEN '01-JUL-2021 12:00:00 AM' AND '30-JUN-2022 11:59:59 PM'
AND (FLOOR(MONTHS_BETWEEN(SYSDATE, PT.BIRTH_DATE)/12)>18)   
AND PT.PATIENT_MRN NOT IN (
SELECT NRS.FULL_TEXT FROM RDM.NOTE_RSLT WHERE NRS.FULL_TEXT LIKE '%rich text%')

Answer 1

当CLOB为<= 4000时，我们可以对CLOB数据使用正常的SQL函数，即足够小，可以将其视为VARCHAR2。但是当我们使用较大的clob时，最好使用内置的Oracle DBMS_LOB包中的函数。

SELECT 
PT.PATIENT_MRN AS MRN
,ORD.PATIENT_ID
,ORD.ORDER_DATE
,ORD.ORDER_TYPE
,ORD.ORDER_PROC
,ORD.SPECIMEN_SOURCE
,ORS.COMPONENT_NAME
,ORS.RESULT_TEXT
,NRS.FULL_TEXT
FROM RDM.PATIENT                PT
LEFT JOIN RDM.ORDERS            ORD ON PT.PATIENT_ID = ORD.PATIENT_ID 
LEFT JOIN RDM.ORDER_RESULT      ORS ON ORS.ORDER_ID = ORD.ORDER_ID
LEFT JOIN RDM.NOTE_RSLT         NRS ON ORD.ORDER_ID = NRS.ORDER_ID
WHERE ORD.ORDER_DATE BETWEEN '01-JUL-2021 12:00:00 AM' AND '30-JUN-2022 11:59:59 PM'
AND (FLOOR(MONTHS_BETWEEN(SYSDATE, PT.BIRTH_DATE)/12)>18)   
AND PT.PATIENT_MRN NOT IN (
SELECT NRS.FULL_TEXT 
FROM RDM.NOTE_RSLT 
WHERE dbms_lob.instr(NRS.FULL_TEXT, '%rich text%') > 0
)   

Answer 2

感谢大家的回复。我能够解决这个问题，通过使用dbms_lob。Substr函数如下:

SELECT 
PT.PATIENT_MRN AS MRN
,ORD.PATIENT_ID AS PAT_ID
,ORD.ORDER_DATE AS ORD_DATE
,ORD.ORDER_TYPE AS ORD_TYPE
,ORD.ORDER_PROC AS ORD_PROC
,ORD.SPECIMEN_SOURCE AS SPECIMEN_SRC
,ORS.COMPONENT_NAME AS NOTE_TYPE
,ORS.RESULT_TEXT AS FINAL_DX
,DBMS_LOB.SUBSTR(NRS.FULL_TEXT, 4000, 1) AS NOTES 
FROM RDM.PATIENT                PT
LEFT JOIN RDM.ORDERS            ORD ON PT.PATIENT_ID = ORD.PATIENT_ID 
LEFT JOIN RDM.ORDER_RESULT      ORS ON ORS.ORDER_ID = ORD.ORDER_ID
LEFT JOIN RDM.NOTE_RSLT         NRS ON ORD.ORDER_ID = NRS.ORDER_ID
WHERE ORD.ORDER_DATE BETWEEN '01-JUL-2021 12:00:00 AM' AND '30-JUN-2022 11:59:59 
PM'
AND (FLOOR(MONTHS_BETWEEN(SYSDATE, PT.BIRTH_DATE)/12)>18) 

之后，我能够搜索一个字符串使用WHERE…不是。CLOB字段大于4000个字符，所以这对我来说很好。