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

Posted on 2011-09-27
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)"


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


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


"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

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,
Question by:B_rider
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
LVL 74

Expert Comment

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

Author Comment

ID: 36711558
Hi thanks sbstuber,
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,

LVL 74

Expert Comment

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

you could try....


this is less efficient, but more accurate if the pattern assumption above is true
Webinar: Security & Encryption in the MySQL world

Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

LVL 74

Expert Comment

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

Author Comment

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
LVL 74

Expert Comment

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
LVL 74

Expert Comment

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

Accepted Solution

sdstuber earned 500 total points
ID: 36711720
if a phone number might not be present and you want to return NULL  then try this...

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


Author Closing Comment

ID: 36712618
Amazing many thanks

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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

691 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