Getting Error "argument '0' Is Out Of Range"

I am getting error 'ORA-01428: argument '0' is out of range'. Subject in question is : regexp_substr(ltrim(pn.pname),'\d+',INSTR(ltrim(pn.pname),'REFERENCE ID=')) when i am scroll

Solution 1:

INSTR(ltrim(pn.pname),'REFERENCE ID=')

is returning 0 (indicating that the substring you are searching for was not found) and if you try to do:

 REGEXP_SUBSTR( value, regex, 0 )

You will get the error:

ORA-01428: argument '0' is out of range

Instead, you could use:

REGEXP_SUBSTR(
  pn.pname,
  'REFERENCE ID="(\d+)"',
  1,                      -- Start from the 1st character
  1,                      -- Find the 1st occurrence
  NULL,                   -- No flags
  1                       -- Return the contents of the 1st capturing group
)