troubleshooting Question

Oracle TO_NUMBER from varchar2 datatype

Avatar of diannagibbs
diannagibbsFlag for United States of America asked on
DatabasesOracle DatabaseDB Reporting Tools
18 Comments1 Solution1422 ViewsLast Modified:
I'm trying to write a select for a report to clean up bad data.  The source is varchar2 and the target is varchar2 but goal is to load into a number datatype.  I have used CASE for the most part but when I add the ELSE to give me the good numbers, it fails.  So, I have read that probably reading from bottom up so this will not work.  Any ideas?  I was also thinking spaces but I've tried TRIM, LTRIM,RTRIM, DECODE and TRANSLATE and nothing works.  Any direction appreciated.

SQL> select rowid DI_ROW_ID, NAME_FIRST_MIDDLE_LAST, PROVIDERS_DATA_DZ_COL,
  2  to_number(CASE
  3  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%A%') then 9999999999
  4  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%B%') then 9999999999
  5  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%C%') then 9999999999
  6  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%D%') then 9999999999
  7  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%E%') then 9999999999
  8  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%F%') then 9999999999
  9  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%G%') then 9999999999
 10  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%H%') then 9999999999
 11  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%I%') then 9999999999
 12  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%J%') then 9999999999
 13  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%K%') then 9999999999
 14  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%L%') then 9999999999
 15  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%M%') then 9999999999
 16  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%N%') then 9999999999
 17  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%O%') then 9999999999
 18  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%P%') then 9999999999
 19  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%Q%') then 9999999999
 20  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%R%') then 9999999999
 21  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%S%') then 9999999999
 22  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%T%') then 9999999999
 23  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%U%') then 9999999999
 24  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%V%') then 9999999999
 25  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%W%') then 9999999999
 26  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%X%') then 9999999999
 27  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%Y%') then 9999999999
 28  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%Z%') then 9999999999
 29  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%.%') then 9999999999
 30  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%?%') then 9999999999
 31  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%_%') then 9999999999
 32  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%/%') then 9999999999
 33  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%*%') then 9999999999
 34  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%@%') then 9999999999
 35  WHEN PROVIDERS_DATA_DZ_COL like UPPER('%-%') then 9999999999
 36  WHEN PROVIDERS_DATA_DZ_COL like UPPER(' ') then 9999999999
 37  WHEN PROVIDERS_DATA_DZ_COL IS null then 9999999999
 38  ELSE PROVIDERS_DATA_DZ_COL
 39  END) DI_PROVIDERS_FIX
 40  from DI_PROVIDERS
 41  where rownum < 2
 42  order by 2
 43  /
ELSE PROVIDERS_DATA_DZ_COL
     *
ERROR at line 38:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR



SQL> select PROVIDERS_DATA_DZ_COL from DI_PROVIDERS where rownum < 2;

PROVIDERS_DATA_DZ_COL
----------------------------------------
962399
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 18 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros