CODEWITHSUNDEEP
sqloracle-databaseplsql
raju
raju

Reputation: 219

procedure pl sql

i am trying to add some special chars in some rows using procedure. i need to add brackets in between phone number.The database consists column phone#.

if phone number is: 9999999999

now i want to add brackets to this phone number:

(999)(999)(9999)

if phone number has already brackets i should not perform any changes to that.

CREATE OR REPLACE PROCEDURE Phoneno
AS
BEGIN
UPDATE PERSON
SET PHONE# = "here i want to add brackets to phone numbers"
END;
/

what should i write in set phone condition so that i can add brackets to phone number.

Upvotes: 0

Views: 55

Answers (2)

Littlefoot
Littlefoot

Reputation: 143163

Here's another option you might consider; I'm removing brackets if they exist, and then format the output using regular expression:

SQL> WITH test (phone)
  2       AS (SELECT '9999999999' FROM DUAL
  3           UNION
  4           SELECT '(123)4567890' FROM DUAL
  5           UNION
  6           SELECT '9874(234)123' FROM DUAL
  7           UNION
  8           SELECT '(123)(456)78' FROM DUAL)
  9  SELECT phone,
 10         REGEXP_REPLACE (REPLACE (REPLACE (phone, '(', ''), ')', ''),
 11                         '(\d{3})(\d{3})(\d{1,4})',
 12                         '(\1)(\2)(\3)')
 13            result
 14    FROM test;

PHONE        RESULT
------------ --------------------
(123)(456)78 (123)(456)(78)
(123)4567890 (123)(456)(7890)
9874(234)123 (987)(423)(4123)
9999999999   (999)(999)(9999)

SQL>

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You can construct this using string operations, using case to see if they are necessary:

(case when phone like '%(%' then phone
      when length(phone) <> 10 then phone
      else '(' || substr(phone, 1, 3) || ')(' || substr(phone, 4, 3) || ')(' || substr(phone, 7, 4)
 end)

Upvotes: 3

Related Questions