Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Pattern recognition in a text field

Posted on 2008-11-07
Medium Priority
Last Modified: 2013-12-19
I am doing some data profiling and want to create a procedure which will provide back the pattern of the data in the text field.  In my first case, this is phone number.  So, for each phone number in a table I have data in, I want to parse through the data element and return the pattern.  For example, if the field was a phone number, I would expect patterns consisting of Numbers (N), Characters (A) and special or delimiting characters.  If I were to pass in data that looked like this:  (123) 234-3456, the return pattern would need to be (NNN) NNN-NNNN.  If the phone looked like this 123.L16.1234, then the return pattern would need to look like this NNN.NNN.NNNN.  I am thinking that the solution will including using regular expressions from Oracle, but I just can't wrap my head around the best way to do this.  Any help would be appreciated.  Thanks!
Question by:RobinBuchanan
  • 2
LVL 27

Accepted Solution

sujith80 earned 500 total points
ID: 22903773
select replace(regexp_replace(regexp_replace(<your column>, '[0-9]', '~'), '[a-zA-Z]', 'A'), '~', 'N') pattern
from <table>;

Author Closing Comment

ID: 31514298
Thank you!  Very quick response, allows me to proceed with my analysis today!
LVL 27

Expert Comment

ID: 22905097
It was a bit too quick and a bit longer.

Use this version instead.
select regexp_replace(regexp_replace(<your column>, '[a-zA-Z]', 'A') , '[0-9]', 'N') pattern
from <your table>;

Open in new window


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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
Suggested Courses
Course of the Month10 days, 16 hours left to enroll

572 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