title: Invalid number error
body: <p>I have a related <a href="https://stackoverflow.com/q/37149802/6297770">question</a> and trying to implement their answer to my problem.
  When I try to use their proposed solution I get ORA-01722: invalid number.</p>
  
  <p>Here is my table</p>
  
  <pre><code>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)
  );
  </code></pre>
  
  <p>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.</p>
  
  <p>These first updates are for determining 100 Ave and 100th Ave as valid.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>When the lines marked as problems are remove, it will run, but the results are not what what I want.</p>
  
  <p>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.</p>
  
  <p>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?</p>