Invalid number error

Question

I have a related question and trying to implement their answer to my problem. When I try to use their proposed solution I get ORA-01722: invalid number.

Here is my table

CREATE TABLE TEMP_PARSE_EXIST
( 
PHYS_ST_ADRS        VARCHAR2(64 CHAR),
PHYS_ADRS_LN2       VARCHAR2(64 CHAR),
PHYS_COM_NM         VARCHAR2(50 CHAR),
PROV                VARCHAR2(10 CHAR),
PSTL_CD             VARCHAR2(16 CHAR),
CNTRY               VARCHAR2(50 CHAR),
MAIL_ADRS           VARCHAR2(64 CHAR),
MAIL_ADRS_LN2       VARCHAR2(64 CHAR),
MAIL_COM_NM         VARCHAR2(50 CHAR),
MAIL_PROV           VARCHAR2(10 CHAR),
MAIL_PSTL_CD        VARCHAR2(16 CHAR),
ADRS                VARCHAR2(64),
V_DIRN              VARCHAR2(2),    
V_NUM               VARCHAR2(8),
V_STREET_NAME       VARCHAR2(64),
V_SECOND_LINE       VARCHAR2(64),
V_STREET_TYPE       VARCHAR2(64),
V_POSTAL            VARCHAR2(7),
V_COMM_NM           VARCHAR2(64),
R_REC_TYP_CD        VARCHAR2(1),
ADT_ACT             VARCHAR2(200 CHAR)
);

When running the next part of the code, it comes after the table gets truncated and repopulated from the original data source. I always make sure to truncate and repopulate when I am running the tests to make sure previous results are not interfering.

These first updates are for determining 100 Ave and 100th Ave as valid.

DECLARE
    ALLOWED_STREETS VARCHAR2(1400 char) := '(ABBEY|ACRES|WHARF|WOOD|WYND)'; --has been cut down for question
BEGIN
--this one is for when the 3rd "word" is one of the street types
--and the 2nd "word" is purly numbers for a building number like 100 street
    UPDATE TEMP_PARSE_EXIST
    SET ADT_ACT = 'CASE 1', V_NUM = REGEXP_SUBSTR(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+'), '\d+$')
    WHERE REGEXP_LIKE(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+\s\w+', 'i'), ALLOWED_STREETS || '$', 'i') --problem line 
    and REGEXP_LIKE(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+'), '^\w+(\s?-\s?)\d+$')
    AND ADT_ACT IS NULL;

--this one is for when the 3rd "word" is one of the street types
--and the 2nd "word" is numbered streets like 1st ave
    UPDATE TEMP_PARSE_EXIST
    SET ADT_ACT = 'CASE 2', V_STREET_NAME = REGEXP_SUBSTR(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+'), '\d+(ST|ND|RD|TH)$')
    WHERE REGEXP_LIKE(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+\s\w+', 'i'), ALLOWED_STREETS || '$', 'i') --problem line
    AND REGEXP_LIKE(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+'), '^\w+(\s?-\s?)\d+(ST|ND|RD|TH)$') 
    AND ADT_ACT IS NULL;
END;

When the lines marked as problems are remove, it will run, but the results are not what what I want.

So what I am doing is working with adrs data that includes building number, sub-units, street name, street type, direction. I am currently working on trying to parse when sub-unit and building number are hyphened together and when they are not. So the conditions I will be placing will be strict for the various methods that can exist.

So while there are numbers in the data I am working with, they are stored as characters, and the fields their being stored in are varchar2, and there are no number operations (that I know of) that are being done. So why the invalid number error, and how can I fix it?

Answer 1

You're calling REGEXP_SUBSTR() as:

REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+\s\w+', 'i')

As you can see from the syntax diagram:

The third argument to that function is the position, and the 'i' you're giving should be the fifth, match_param. Oracle is trying to interpret the letter i as a number since it expects one for the position argument, hence the error you're getting.

You need to supply the position and occurrence arguments too, e.g.:

REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+\s\w+', 1, 1, 'i')

I'm not sure you need the case-insensitive flag for that though; you do for the REGEXP_LIKE as you want to match ABBEY, Abbey, abbey etc., but it doesn't seem to be useful for the substring extraction; so you can also probably do:

REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+\s\w+')