1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

What is the query to display the Phone Number in sepearte columns?

Discussion in 'SQL PL/SQL' started by Bagavathi, Dec 26, 2014.

  1. Bagavathi

    Bagavathi Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    We have one column “phone number” in employees table which is stored as
    011.44.1344.429268 , separated with a dot.
    I need a query to separate each part and display the result as :
     

    Attached Files:

  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Bagavathi,

    One way of doing it is by using REGEXP_SUBSTR (or you can use REGEXP_REPLACE also):

    Code (SQL):
    WITH phone_nos AS (
    SELECT '515.124.4569' n FROM DUAL UNION ALL
    SELECT '011.44.1344.429268' FROM DUAL
    )
    SELECT
       n,
       REGEXP_SUBSTR(n, '([[:digit:]]*)(\.|$)', 1, 1, 'i',1 ) "FIRST",
       REGEXP_SUBSTR(n, '([[:digit:]]*)(\.|$)', 1, 2, 'i',1 ) "SECOND",
       REGEXP_SUBSTR(n, '([[:digit:]]*)(\.|$)', 1, 3, 'i',1 ) "THIRD",
       REGEXP_SUBSTR(n, '([[:digit:]]*)(\.|$)', 1, 4, 'i',1 ) "FOURTH"
    FROM phone_nos;
    With "older" versions of Oracle, you may use SUBSTR with a combination of INSTR....
     
    Bagavathi likes this.
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    hi.

    additional link with examples :
    http://oracle-base.com/articles/misc/regular-expressions-support-in-oracle.php


    it is possible to simplify a mask, in an example the provide Rajen,
    if the separator of groups and a format isn't important :
    Code (SQL):

    WITH phone_nos AS (
    SELECT '515.124.4569' n FROM dual UNION ALL
    SELECT '011.44.1344.429268' FROM dual
    )
    SELECT
       n,
       regexp_substr(n, '(\d+)', 1, 1, 'i',1 ) "first",
       regexp_substr(n, '(\d+)', 1, 2, 'i',1 ) "second",
       regexp_substr(n, '(\d+)', 1, 3, 'i',1 ) "third",
       regexp_substr(n, '(\d+)', 1, 4, 'i',1 ) "fourth"
    FROM phone_nos;
     
     
    Bagavathi likes this.