| String manipulation | Example | LOB equivalent | Notes |
|---|
| Length | length(str) | dbms_lob.getlength(lobpointer) | |
| Substring | substr(str,startpos,[len]) | dbms_lob.substr(lobpointer, [len], [offset]) | Note reversal of 2nd and 3rd arguments. offset is like startpos | |
| Find in string | instr(str,pattern,[startpos],[nth]) | dbms_lob.instr(lobpointer, pattern, [offset], [nth]) | "pattern" is in raw | |
| Append | str1 := str1 || str2 | dbms_lob.append(lobpointer1, lobpointer2) | lobpointer1 and lobpointer2 must be internal LOBs; can't use in SQL |
| Set string value in table to null | update mytable set mystrfield = NULL; | update mytable set myclobfield=EMPTY_CLOB(); update mytable set myblobfield=EMPTY_BLOB(); | The LOB data is set to Null, but the LOB locater in myclobfield is set to a valid pointer. dbms_lob functions will error out if passed a null pointer. |
| Compare | if (str1 = str2) then... | if ( dbms_lob.compare(lobloc1, lobloc2) = 0 ) then... | |