REGEXP_SUBSTR: Create a list from a comma separated line

You can create a numbered list from a comma separated string line using REGEXP_SUBSTR function.

WITH t AS

   (SELECT 'PAYABLES, RECEIVABLES, GENERAL LEDGER, INVENTORY, PURCHASING'  str,

           '[^|,]+'  sep

      FROM dual)

 SELECT level, TRIM (REGEXP_SUBSTR (t.str, t.sep, 1, LEVEL)) parsed_str

   FROM t

CONNECT BY LEVEL <= REGEXP_COUNT (t.str, t.sep);

Query to test if a value is a number or string

Using Oracle's REGEXP_LIKE function, you can easily find out whether a value is a number or a string.

-------------------------------------------------------------------------------

-- Query to test if a value is a number of string

-------------------------------------------------------------------------------

DECLARE

   test_string  VARCHAR2(80);

   v_string     VARCHAR2(80);

  

BEGIN

  

   test_string := '123';

   -- test_string := 'A123';

  

   SELECT CASE

             WHEN REGEXP_LIKE(test_string, '^-?[[:digit:],.]*$') THEN

                test_string || ' is a NUMBER'

             ELSE

                test_string || ' is a STRING'

          END

     INTO v_string

     FROM dual;

  

   DBMS_OUTPUT.PUT_LINE(v_string);

  

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);

END;