Solved

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

Posted on 2011-09-27
9
404 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36711502
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
ID: 36711558
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 74

Expert Comment

by:sdstuber
ID: 36711569
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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 74

Expert Comment

by:sdstuber
ID: 36711582
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
 

Author Comment

by:B_rider
ID: 36711615
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 74

Expert Comment

by:sdstuber
ID: 36711699
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 74

Expert Comment

by:sdstuber
ID: 36711707
note,  the 10 zeros is intentional even though there are 11 characters (10 digits plus hyphen)
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36711720
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
ID: 36712618
Amazing many thanks
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query Syntax Assistance 2 35
SQL Syntax 6 32
MS SQL Server Management Studio R2 4 32
T-SQL: Need Group By to use "fuzzy logic"?? 3 23
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

733 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