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
  • 6
  • 3
LVL 73

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 73

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
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!

LVL 73

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 73

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 73

Expert Comment

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

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
powershell code to list items in dropdown 6 56
Select and Insert Query running slow 4 32
Problem with duplicate records in Oracle query 16 24
SQL Error - Query 6 24
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

816 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

13 Experts available now in Live!

Get 1:1 Help Now