Solve the Problem of comparing the clob fields in Oracle

Although the clob and BLOB fields in Oracle meet the requirements for storing ultra-large content during development, they often cause troubles in some simple usage. Clob stores pointers and cannot directly obtain the actual values. The text field in sqlserver is very convenient and can be directly compared with the required string. What is equal. However, changing to Oracle is troublesome. To open up a cache and read and convert the content segment, is it so complicated to write a where condition? After seeking information from multiple parties, we finally found a convenient and simple method: using the methods in the dbms_lob package (Rest assured, built-in) instr and substr. The details are as follows:

 

Instr and substr Functions

 

The instr function is used to find the nth pattern matching string from a large object starting from the specified position.
The instr function syntax is as follows:

1:  dbms_lob.instr(

2:  lob_loc in clob character set any_cs,

3:  pattern in varchar2 character set lob_loc%charset,

4:  offset in integer:=1,

5:  nth in integer := 1)

6:  return integer; 

7:   

 

Lob_loc is the internal large object Positioner

Pattern is the pattern to be matched

Offset is the start position of the file to be matched.

Nth is the nth match to be performed

Substr Function

The substr function is used to extract the specified number of bytes from a large object. This function is usually used when we only need a part of a large object.

The substr function Syntax of an internal large object is as follows:

The meanings of parameters are as follows:

Lob_loc is the large object positioner to be operated by the substr function.

Amount is the number of bytes to be extracted from a large object.

Offset refers to the position where a large object starts to extract data.

If data is successfully extracted from a large object, this function returns a raw value. If so, null is returned:

1 null at the end of any input parameter

2 amount <1

3 amount> 32767

4 offset <1

5 Offset> lobmaxsize

 1:  dbms_lob.instr(

 2:  lob_loc in blob, 

 3:  pattern in raw, 

 4:  offset in integer := 1;

 5:  nth in integer := 1)

 6:  return integer; 

 7:  dbms_lob.substr(

 8:    lob_loc in blob, 

 9:    amount in integer := 32767,

10:    offset in integer := 1)

11:  return raw; 

12:  dbms_lob.substr(

13:    lob_loc in clob character set any_cs, 

14:    amount in integer := 32767,

15:    offset in integer := 1)

16:  return varchar2 character set lob_loc%charset; 

 

As shown in the following example, it is easy to understand:

 1:  declare 

 2:     source_lob clob;

 3:     pattern varchar2(6) := 'Oracle';

 4:     start_location integer := 1;

 5:     nth_occurrence integer := 1;

 6:     position integer;

 7:     buffer varchar2(100);

 8:  begin

 9:     select clob_locator into source_lob from mylobs where lob_index = 4;

10:     position := dbms_lob.instr(source_lob, pattern, start_location, nth_occurrence);

11:     dbms_output.put_line('The first occurrence starts at position:' || position);

12:   

13:     nth_occurrence := 2;

14:     select clob_locator into source_lob from mylobs where lob_index = 4;

15:     position := dbms_lob.instr(source_lob, pattern, start_location, nth_occurrence);

16:     dbms_output.put_line('The first occurrence starts at position:' || position);

17:   

18:     select clob_locator into source_lob from mylobs where lob_index = 5;

19:     buffer := dbms_lob.substr(source_lob, 9, start_location);

20:     dbms_output.put_line('The substring extracted is: ' || buffer);

21:  end; 

 

Output result:

The first occurrence starts at position: 8

The first occurrence starts at position: 24

The substring extracted is: Oracle 9i

 

Haha, it's easy to match and filter clob fields without a lot of stored procedures and functions. Good luck!