The following is an illustration of  taking a string containing numbers separated by commas, and finding the greatest and least of it.

 

SQL> VAR TEST_VAR VARCHAR2(40);

SQL> EXEC :TEST_VAR := '1,2,3,4' ;

 

PL/SQL procedure successfully completed.

 

SQL> SELECT SUBSTR(:TEST_VAR,1,1) FROM DUAL;

 

S

-

1

 

1 row selected.

 

SQL>  SELECT  SUBSTR(:TEST_VAR, INSTR(:TEST_VAR,',',2)+1,1) FROM DUAL;

 

S

-

2

 

1 row selected.

 

SQL>  SELECT  SUBSTR(:TEST_VAR, INSTR(:TEST_VAR,',',1,2)+1,1) FROM DUAL;

 

S

-

3

 

1 row selected.

 

SQL>  SELECT  SUBSTR(:TEST_VAR, INSTR(:TEST_VAR,',',1,3) +1,1) FROM DUAL;

 

S

-

4

 

1 row selected.

 

SQL> SELECT SUBSTR(:TEST_VAR,1,1) FROM DUAL;

 

S

-

1

 

1 row selected.

 

SQL>

SQL>

SQL>

SQL>

SQL> SELECT GREATEST ( SUBSTR(:TEST_VAR,1,1),SUBSTR(:TEST_VAR, INSTR(:TEST_VAR,',',2)+1,1) , SUBSTR(

:TEST_VAR, INSTR(:TEST_VAR,',',1,2)+1,1), SUBSTR(:TEST_VAR, INSTR(:TEST_VAR,',',1,3) +1,1) )

  2  FROM DUAL;

 

G

-

4

 

1 row selected.

 

SQL> ED

Wrote file afiedt.buf

 

  1  SELECT LEAST ( SUBSTR(:TEST_VAR,1,1),SUBSTR(:TEST_VAR, INSTR(:TEST_VAR,',',2)+1,1) , SUBSTR(:TE

  2* FROM DUAL

SQL> /

 

L

-

1

 

1 row selected.