Prevent ORA-01722: invalid number in Oracle

Question

I have this query

  SELECT text
         FROM book
              WHERE lyrics IS NULL
            AND MOD(TO_NUMBER(SUBSTR(text,18,16)),5) = 1

sometimes the string is something like this $OK$OK$OK$OK$OK$OK$OK, sometimes something like @P,351811040302663;E,101;D,07112018134733,07012018144712;G,4908611,50930248,207,990;M,79379;S,0;IO,3,0,0

if I would like to know if it is possible to prevent ORA-01722: invalid number, because is some causes the char in that position is not a number. I run this query inside a procedure a process all the rows in a cursor, if 1 row is not a number I can't process any row

Why do you store important numbers as a part of a character string? — jarlh, Jan 16 '18 at 15:07
@jarlh - sometimes that is not the poster's (or even the entire database industry's) choice (fault). Take the VIN (Vehicle Identification Number) for example. A table may store VIN for the cars in a car rental business. It's a long single string. Different parts of it (some alphabetic characters, some numeric) encode various things. The DB must store the VIN. Even if one wants to extract the different bits and store them separately, they will still need to do something like the OP is doing. Right? — , Jan 16 '18 at 15:13
@timbiegeleisen - To those who closed the question as an exact duplicate: It isn't. The question you pointed to asks how to find the values that are non-numeric. In this thread, the OP is asking how to prevent ORA-01722. It's a different question, even though they are related. If you want to close it as a duplicate, please find an exact one. — , Jan 16 '18 at 15:16
Define what you mean by "prevent ORA-01722". For example, one way to prevent it is to remove that condition from the WHERE clause; obviously that is not what you want, but what you **do** want is not entirely clear. What should happen if the substring is NOT numeric? Should the WHERE clause evaluate to TRUE or to FALSE? — , Jan 16 '18 at 15:19

Kaushik Nayak · Accepted Answer · 2018-01-16T18:18:54.113

1

You could use VALIDATE_CONVERSION if it's Oracle 12c Release 2 (12.2),

WITH book(text) AS
  (SELECT '@P,351811040302663;E,101;D,07112018134733,07012018144712;G,4908611,50930248,207,990;M,79379;S,0;IO,3,0,0'
   FROM DUAL
   UNION ALL SELECT '$OK$OK$OK$OK$OK$OK$OK'
   FROM DUAL
   UNION ALL SELECT '12I45678912B456781234567812345671'
   FROM DUAL)
SELECT *
FROM book
WHERE CASE
          WHEN VALIDATE_CONVERSION(SUBSTR(text,18,16) AS NUMBER) = 1 
           THEN MOD(TO_NUMBER(SUBSTR(text,18,16)),5)
          ELSE 0
      END = 1 ;

Output

TEXT
12I45678912B456781234567812345671

edited Jan 16 '18 at 18:18

answered Jan 16 '18 at 17:53

Kaushik Nayak

30,772
5
32
45

1

The predicate could be simplified with a different 12.2 feature: `mod(to_number(SUBSTR(text,18,16) default 0 on conversion error),5) = 1` – Jon Heller Jan 17 '18 at 04:39

hakobot · Answer 2 · 2018-01-16T16:36:31.347

0

        SELECT text
     FROM book
          WHERE lyrics IS NULL
        AND case when regexp_like(SUBSTR(text,18,16),'^[^a-zA-Z]*$') then MOD(TO_NUMBER(SUBSTR(text,18,16)),5)
            else null 
            end = 1;

edited Jan 16 '18 at 16:36

answered Jan 16 '18 at 16:04

hakobot

186
2
9

1

The OP in the question already shows non-letter, non-digit characters in the input strings, so checking for letter only is insufficient. Also, in the `then` clause, what do you mean by `text( ..... )`? There is no such function. – Jan 16 '18 at 16:20

score 0 · Answer 3 · answered Jan 16 '18 at 16:30

Assuming the condition should be true if and only if the 16-character substring starting at position 18 is made up of 16 digits, and the number is equal to 1 modulo 5, then you could write it like this:

...
where .....
  and case when translate(substr(text, 18, 16), 'z0123456789', 'z') is null
                and substr(text, 33, 1) in ('1', '6')
           then 1 end
      = 1

This will check that the substring is made up of all-digits: the translate() function will replace every occurrence of z in the string with itself, and every occurrence of 0, 1, ..., 9 with nothing (it will simply remove them). The odd-looking z is needed due to Oracle's odd implementation of NULL and empty strings (you can use any other character instead of z, but you need some character so no argument to translate() is NULL). Then - the substring is made up of all-digits if and only if the result of this translation is null (an empty string). And you still check to see if the last character is 1 or 6.

Note that I didn't use any regular expressions; this is important if you have a large amount of data, since standard string functions like translate() are much faster than regular expression functions. Also, everything is based on character data type - no math functions like mod(). (Same as in Thorsten's answer, which was only missing the first part of what I suggested here - checking to see that the entire substring is made up of digits.)

Prevent ORA-01722: invalid number in Oracle

3 Answers3