CODEWITHSUNDEEP
oracle-databaseplsql
mr.kalvados
mr.kalvados

Reputation: 47

Display 'NULL' instead of empty row in pl/sql function

Got the following pl/sql function that extracts numbers from the string:

CREATE FUNCTION extract_number (
in_number VARCHAR2)
RETURN NUMBER IS
BEGIN
RETURN regexp_replace(in_number, '[^[:digit:]]', '');
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;

Need to put NULL where no numeric value was found (instead of just empty row). Example:

Got a table:

create table tab2 (val varchar2(100));
insert into tab2 (val) values ('2133jdhfjshd4');
insert into tab2 (val) values ('afafaf');
insert into tab2 (val) values ('skdjfj6787887hhh');
insert into tab2 (val) values ('324824898');
insert into tab2 (val) values ('4jh4jhh4');
commit;

This is an output from function:

          21334

        6787887
      324824898
            444

On the second row I need 'NULL' to be placed.

NO_DATA_FOUND not working. Please advise what should I do?

Upvotes: 1

Views: 2496

Answers (3)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

There is no need of NO_DATA_FOUND . However you can raise it anyway..

CREATE FUNCTION extract_number (in_number VARCHAR2)
       RETURN NUMBER
    IS
       v_return   NUMBER (10);
    BEGIN
       v_return := REGEXP_REPLACE (in_number, '[^[:digit:]]', '');

       IF v_return IS NULL
       THEN
          RAISE NO_DATA_FOUND;
       END IF;

       RETURN v_return;
    EXCEPTION
       WHEN NO_DATA_FOUND
       THEN
          RETURN NULL;
    END;

Upvotes: 0

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

In sqlplus you can use this set null 'null'. And this is only for presentation.

I see few problem with your function.

  1. regexp_replace never throws no_data_found exception
  2. The result of regexp_replace is always varchar2 or null and oracle has to do the implicit conversion to the number.

Upvotes: 0

Gary_W
Gary_W

Reputation: 10360

Wrap the regexp_replace in NVL():

NVL(regexp_replace(in_number, '[^[:digit:]]', ''), 'NULL');

If NULL is returned by regexp_replace(), return the string 'NULL'.

Documentation

Upvotes: 1

Related Questions