CODEWITHSUNDEEP
sqloracle-database
Animesh Sheolikar
Animesh Sheolikar

Reputation: 75

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 scrolling for more records it is giving that error.

example :

 pname                                           regexp value

FragIT<REFERENCE ID="6998"                        6998
TYPE="trademark"/> MicroSpin

i am using total query like this :

 SELECT pname,
 regexp_substr(ltrim(pn.sys_name_text),'\d+',INSTR(ltrim(pn.sys_name_text),
 'REFERENCE ID=')) comm from products p
 left join product_names pn using(product_id)
 where pname like '%trademark%' and language_id  = 1
 and regexp_count(pname,'trademark') <= 1

Here refrence tag may come more than once thats why putted last condition. can you please help on this.

Upvotes: 0

Views: 1471

Answers (1)

MT0
MT0

Reputation: 168232

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
)

Upvotes: 1

Related Questions