RE: Conversion of a varchar2 to a number

From: larry elkins <elkinsl_at_flash.net>
Date: Wed, 13 Dec 2000 19:08:36 -0600
Message-Id: <10709.124518@fatcity.com>

Dave,

To expand on the character function and parsing approach, and I don't consider this any better or worse than any other approaches, here is a "pure" SQL example that you might consider:

SQL> select * from strip
2 /

STRIP

214-555-1212
(214) 555 1212
1234A
1234A
2145551212

SQL> select strip
  2 from strip
  3 where lpad(translate(strip,'0123456789','9999999999'),15,'9') = '999999999999999'
  4 /

STRIP

2145551212

The obvious key is the WHERE clause. Keep indexing issues in mind if they apply. The above isn't foolproof in determining if something is a number; but, it might be useful in your scenario.

The values 0,1,2,3,4,5,6,7,8, and 9 are all translated to 9's (the use of the 9 itself in the "from" and "to" is actually redundant since it would remain the same).
Any other character would remain as itself -- e.g. 1234A would become 9999A
Do an LPAD with 9's to a length of 15 to account for variable length strings in the column -- e.g. 1234A would now be 99999999999999A. 15 in this case is the maximum length of my column "strip" -- use and adjust accordingly if you need the LPAD.
If it doesn't equal the fifteen 9's, then there is something other than 0,1,2,3,4,5,6,7,8, or 9 in the column and the row is excluded.

The above is of limited use, and, there are other issues as well; but, for your case, it, or a variation of it, might be something to consider.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of David Turner
Sent: Wednesday, December 13, 2000 12:46 PM To: Multiple recipients of list ORACLE-L Subject: Conversion of a varchar2 to a number

I have a user that has a phone number field that is stored as a varchar2 and some of the data actually has letters in it. When he tries to do a TO_NUMBER it fails of course on the records that have letters in them.

To perform the conversion I thought using a cursor and handling the exceptions would work, but wanted to see if other people had a better Received on Wed Dec 13 2000 - 19:08:36 CST