Pattern recognition in a text field

Posted on 2008-11-07
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
    LVL 27

    Accepted Solution

    select replace(regexp_replace(regexp_replace(<your column>, '[0-9]', '~'), '[a-zA-Z]', 'A'), '~', 'N') pattern
    from <table>;

    Author Closing Comment

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

    Expert Comment

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    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…
    Via a live example, show how to take different types of Oracle backups using RMAN.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    761 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

    12 Experts available now in Live!

    Get 1:1 Help Now