[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

SQL Oracle: Finding / Parsing numerical string out of text field

I have a table ie temp_memo which has one column called memo.

typical values are

"called number (123-456-7890) then spoke to bob with account number (123456)"

or

"account number 123456, number that dial was 1234567890 name was bob"

or

"account number 123456, number 123-456-7890 bob called to"

or

"bob called his number was (1234567890), caller id number is 123456"

Basically I want to create a script that isolates and populates on the phone number in the format
1234567890.

Ideally I would like to do in SQL but I have other tools at disposal such as excel.

Any help would be greatly appreciated.

Many thanks,
0
B_rider
Asked:
B_rider
  • 6
  • 3
1 Solution
 
sdstuberCommented:
select regexp_substr(replace(memo,'-'),'[0-9]{10}') from temp_memo

I made the assumption that the phone number will be the only 10digit numeric substring

if that's not true, please explain what rules I can use to identify a phone number from other numbers
0
 
B_riderAuthor Commented:
Hi thanks sbstuber,
running:
select regexp_substr(replace(memo,'-'),'[0-9]{10}') from temp_memo_test

getting invalid column name for regexp_substr

running oracle 8i right now, think this is a versioning issue? if so is there anyway around?

Many Thanks,

0
 
sdstuberCommented:
alternately,  if phone numbers will always be of the form 1234567890 or 123-456-7890

you could try....


 replace(regexp_substr(memo,'[0-9]{10}|([0-9]{3}-){2}[0-9]{4}'),'-')

this is less efficient, but more accurate if the pattern assumption above is true
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sdstuberCommented:
yes,  8i doesn't have regular expressions.


that's woefully out of date,  I highly recommend upgrading.

however, assuming that's not an immediate option I'll see what I can come up with
0
 
B_riderAuthor Commented:
yah I'm aware of the versioning problems and would need to sift through a yeah long hike through the jungle of IT buearcary to get it upgraded
many thanks for any attempts
0
 
sdstuberCommented:
try this...

SUBSTR(replace(memo,'-'), INSTR(TRANSLATE(memo, '0123456789-', '0000000000'), '0000000000'), 10)

this assumes all phone numbers are of one of these formats: 1234567890 or 123-456-7890
it also assumes every line has a phone number
0
 
sdstuberCommented:
note,  the 10 zeros is intentional even though there are 11 characters (10 digits plus hyphen)
0
 
sdstuberCommented:
if a phone number might not be present and you want to return NULL  then try this...



DECODE(
           INSTR(TRANSLATE(memo, '0123456789-', '0000000000'), '0000000000'),
           0, NULL,
           SUBSTR(REPLACE(memo, '-'),
                  INSTR(TRANSLATE(memo, '0123456789-', '0000000000'), '0000000000'),
                  10
                 ))

0
 
B_riderAuthor Commented:
Amazing many thanks
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now