How to query clob field data in oracle

In Oracle, you can use the instr() function with the select statement to query clob field data. The instr function is used to determine whether a string contains specified characters. The syntax is "select*from table name...instr( Field name (clod type),'query condition',1,1)".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to query clob field data in oracle

Syntax

select * from table_name where dbms_lob.instr(字段名（clod类型）,'查询条件',1,1) > 0;

Specific examples

select t.* t.rowid from **BUSI_MESSAGE_LOG t
wheredbms_lob.instr(t.mesbody,'2017',1,1) > 0;

Syntax explanation

In Oracle, you can use instr The function judges a string to determine whether it contains specified characters. The syntax is:

instr(sourceString,destString,start,appearPosition)。

eg: dbms_lob.instr(field name (clod type),'query condition',1,1)

• where sourceString represents what you want Query field;

• destString represents the query condition;

• start represents the starting position of the search. This parameter is optional and defaults to 1;

• appearPosition represents how many occurrences of destString you want to find from the source character. This parameter is also optional and defaults to 1;

If the value of start is a negative number, it means searching from right to left.

The return value is: when the target string is not found in the source string, 0 is returned; when the source string or target string is null, null is returned;

Return in other cases The actual position of the target string in the source string

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to query clob field data in oracle. For more information, please follow other related articles on the PHP Chinese website!