Concatenation

||, CONCAT()

Select clobCol || clobCol2 from tab;

Yes

Yes

Comparison

= , !=, >, >=, <, <=, <>, ^=

if clobCol=clobCol2 then...

No

Yes

Comparison

IN, NOT IN

if clobCol NOT IN (clob1, clob2, clob3) then...

No

Yes

Comparison

SOME, ANY, ALL

if clobCol < SOME (select clobCol2 from...) then...

No

N/A

Comparison

BETWEEN

if clobCol BETWEEN clobCol2 and clobCol3 then...

No

Yes

Comparison

LIKE [ESCAPE]

if clobCol LIKE '%pattern%' then...

Yes

Yes

Comparison

IS [NOT] NULL

where clobCol IS NOT NULL

Yes

Yes

Character Functions

INITCAP, NLS_INITCAP

select INITCAP(clobCol) from...

CNV

CNV

Character Functions

LOWER, NLS_LOWER, UPPER, NLS_UPPER

...where LOWER(clobCol1) = LOWER(clobCol2)

Yes

Yes

Character Functions

LPAD, RPAD

select RPAD(clobCol, 20, ' La') from...

Yes

Yes

Character Functions

TRIM, LTRIM, RTRIM

...where RTRIM(LTRIM(clobCol,'ab'), 'xy') = 'cd'

Yes

Yes

Character Functions

REPLACE

select REPLACE(clobCol, 'orig','new') from...

Yes

Yes

Character Functions

SOUNDEX

...where SOUNDEX(clobCOl) = SOUNDEX('SMYTHE')

CNV

CNV

Character Functions

SUBSTR

...where substr(clobCol, 1,4) = like 'THIS'

Yes

Yes

Character Functions

TRANSLATE

select TRANSLATE(clobCol, '123abc','NC') from...

CNV

CNV

Character Functions

ASCII

select ASCII(clobCol) from...

CNV

CNV

Character Functions

INSTR

...where instr(clobCol, 'book') = 11

Yes

Yes

Character Functions

LENGTH

...where length(clobCol) != 7;

Yes

Yes

Character Functions

NLSSORT

...where NLSSORT (clobCol,'NLS_SORT = German') > NLSSORT ('S','NLS_SORT = German')

CNV

CNV

Character Functions

INSTRB, SUBSTRB, LENGTHB

These functions are supported only for CLOBs that use single-byte character sets. (LENGTHB is supported for BLOBs and CLOBs.)

Yes

Yes

Character Functions - Regular Expressions

REGEXP_LIKE

This function searches a character column for a pattern. Use this function in the WHERE clause of a query to return rows matching the regular expression you specify.

Yes

Yes

Character Functions - Regular Expressions

REGEXP_REPLACE

This function searches for a pattern in a character column and replaces each occurrence of that pattern with the pattern you specify.

Yes

Yes

Character Functions - Regular Expressions

REGEXP_INSTR

This function searches a string for a given occurrence of a regular expression pattern. You specify which occurrence you want to find and the start position to search from. This function returns an integer indicating the position in the string where the match is found.

Yes

Yes

Character Functions - Regular Expressions

REGEXP_SUBSTR

This function returns the actual substring matching the regular expression pattern you specify.

Yes

Yes

Conversion

CHARTOROWID

CHARTOROWID(clobCol)

CNV

CNV

Conversion

COMPOSE

COMPOSE('string')

Returns a Unicode string given a string in the data type CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, NCLOB.

CNV

CNV

Conversion

DECOMPOSE

DECOMPOSE('str' [CANONICAL | COMPATIBILITY] )

Valid for Unicode character arguments.

CNV

CNV

Conversion

HEXTORAW

HEXTORAW(CLOB)

No

CNV

Conversion

CONVERT

select CONVERT(clobCol,'WE8DEC','WE8HP') from...

Yes

CNV

Conversion

TO_DATE

TO_DATE(clobCol)

CNV

CNV

Conversion

TO_NUMBER

TO_NUMBER(clobCol)

CNV

CNV

Conversion

TO_TIMESTAMP

TO_TIMESTAMP(clobCol)

No

CNV

Conversion

TO_MULTI_BYTE

TO_SINGLE_BYTE

TO_MULTI_BYTE(clobCol)

TO_SINGLE_BYTE(clobCol)

CNV

CNV

Conversion

TO_CHAR

TO_CHAR(clobCol)

Yes

Yes

Conversion

TO_NCHAR

TO_NCHAR(clobCol)

Yes

Yes

Conversion

TO_LOB

INSERT INTO... SELECT TO_LOB(longCol)...

Note that TO_LOB can only be used to create or insert into a table with LOB columns as SELECT FROM a table with a LONG column.

N/A

N/A

Conversion

TO_CLOB

TO_CLOB(varchar2Col)

Yes

Yes

Conversion

TO_NCLOB

TO_NCLOB(varchar2Clob)

Yes

Yes

Aggregate Functions

COUNT

select count(clobCol) from...

No

N/A

Aggregate Functions

MAX, MIN

select MAX(clobCol) from...

No

N/A

Aggregate Functions

GROUPING

select grouping(clobCol) from... group by cube (clobCol);

No

N/A

Other Functions

GREATEST, LEAST

select GREATEST (clobCol1, clobCol2) from...

No

CNV

Other Functions

DECODE

select DECODE(clobCol, condition1, value1, defaultValue) from...

CNV

CNV

Other Functions

NVL

select NVL(clobCol,'NULL') from...

Yes

Yes

Other Functions

DUMP

select DUMP(clobCol) from...

No

N/A

Other Functions

VSIZE

select VSIZE(clobCol) from...

No

N/A

Unicode

INSTR2, SUBSTR2, LENGTH2, LIKE2

These functions use UCS2 code point semantics.

No

CNV

Unicode

INSTR4, SUBSTR4, LENGTH4, LIKE4

These functions use UCS4 code point semantics.

No

CNV

Unicode

INSTRC, SUBSTRC, LENGTHC, LIKEC

These functions use complete character semantics.

No

CNV