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

Question

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:

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

NO_DATA_FOUND not working. Please advise what should I do?

Warning: don't mess with data types! If the return value is NULL, why do you want to replace that with a four-letter string? As you saw already, using one of the proposed solutions, you will have to change the data type of the return - from number to varchar2. Presumably a function like this would be used to extract numbers from strings, to be used in further computations - as numbers. Change the function to return varchar2 instead, and a lot of other things will break. If you need to display "NULL" on reports, do it in the reporting layer, not in the function itself. — user5683823
– user5683823, Commented Aug 31, 2017 at 14:38

Gary_W · Accepted Answer · 2017-08-31 13:14:18Z

1

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

edited Aug 31, 2017 at 13:14

answered Aug 31, 2017 at 13:08

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mr.kalvados Over a year ago

worked after replaced RETURN NUMBER in function to RETURN VARCHAR2

Arkadiusz Łukasiewicz · Accepted Answer · 2017-08-31 13:14:49Z

0

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

I see few problem with your function.

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

answered Aug 31, 2017 at 13:14

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

Comments

Kaushik Nayak · Accepted Answer · 2017-08-31 13:26:32Z

0

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;

answered Aug 31, 2017 at 13:26

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related