Solved

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

Posted on 2011-09-27
9
383 Views
Last Modified: 2012-08-14
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
Comment
Question by:B_rider
  • 6
  • 3
9 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 

Author Comment

by:B_rider
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:B_rider
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
note,  the 10 zeros is intentional even though there are 11 characters (10 digits plus hyphen)
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:B_rider
Comment Utility
Amazing many thanks
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now