Oracle SQL inserting parenthesis into phone number

Question

The original format:

123-456-7890

My goal format:

(123)456-7890

I wanted to go the route of concatenating between substrings but I continually get flagged for errors. I am unsure of a better method to go about implementing a way to format.

My query:

select || '(' || substr(telephone,0, 3)|| ')' || 
substr(telephone,4, 3)|| ' '|| 
substr(telephone,7, 4)) as telephone,
from book;

My current error:

"missing expression"

Yes which is inconvenient. There is documentation for other parenthesis insertion just not in the same environment as this. — , Feb 04 '16 at 06:00

Tim Biegeleisen · Accepted Answer · 2016-02-04T06:34:59.247

0

You have an extra dangling parenthesis at the end of your SELECT, and you also have a dangling concatenation operator || in the front. Try this:

SELECT '(' || SUBSTR(telephone, 0, 3) || ')' ||
    SUBSTR(telephone, 4, 3) || ' ' || SUBSTR(telephone, 7, 4) AS telephone
FROM book

Update:

You should really use this query, because it turns out you also had a problem with forming your desired output as well:

SELECT '(' || SUBSTR(telephone, 1, 3) || ')' || SUBSTR(telephone, 5, 8) AS telephone
FROM book

edited Feb 04 '16 at 06:34

answered Feb 04 '16 at 05:55

Tim Biegeleisen

502,043
27
286
360

1

@lolocpp Oh dear...you had a `||` operator in _front_ of the `SELECT`. It should run now as I have removed this. – Tim Biegeleisen Feb 04 '16 at 06:01
It is incorrect, since it would give output `(123)-45 6-78`. But, you need `(123)456-7890`. There is no need of `' '` i.e. a space. – Lalit Kumar B Feb 04 '16 at 06:08

Lalit Kumar B · Answer 2 · 2016-02-04T06:15:42.457

Using SUBSTR:

SQL> WITH sample_data AS(
  2  SELECT '123-456-7890' num FROM dual
  3  )
  4  -- end of sample_data mimicking real table
  5  SELECT num,
  6    '('
  7    || SUBSTR(num, 1, 3)
  8    ||
  9    ')'
 10    || SUBSTR(num, 5, 8) AS my_num
 11  FROM sample_data;

NUM          MY_NUM
------------ ---------------
123-456-7890 (123)456-7890

SQL>

Remember, the index for SUBSTR starts from 1. It is bad practice to use 0 as starting index.

You could also do it using REGEXP_REPLACE.

Pattern: (\d{3})(-)(\d{3})(-)(\d{4})

Expression: regexp_replace(num, '(\d{3})(-)(\d{3})(-)(\d{4})', '(\1)\3\4\5')

For example,

SQL> WITH sample_data AS(
  2  SELECT '123-456-7890' num FROM dual
  3  )
  4  -- end of sample_data mimicking real table
  5  SELECT num,
  6    regexp_replace(num, '(\d{3})(-)(\d{3})(-)(\d{4})', '(\1)\3\4\5') my_num
  7  FROM sample_data;

NUM          MY_NUM
------------ ---------------
123-456-7890 (123)456-7890

SQL>

score 0 · Answer 3 · answered Feb 04 '16 at 06:09

0

You can use regular expressions to do.

select regexp_replace
(phoneNo,
  '([[:digit:]]{3})\-([[:digit:]]{3})\-([[:digit:]]{4})',
  '(\1)\2-\3'
) 
from(
select '123-456-7890' as phoneNo from dual)

Output

(123)456-7890

answered Feb 04 '16 at 06:09

Utsav

7,914
2
17
38

score 0 · Answer 4 · answered Feb 04 '16 at 06:12

0

SELECT '123-456-7890','('||SUBSTR('123-456-7890',1,3)||')'||SUBSTR('123-456-7890',5,8) FROM dual;

answered Feb 04 '16 at 06:12

Prashant Mishra

619
9
25

Oracle SQL inserting parenthesis into phone number

4 Answers4