Can (should) I include elements of TO_CHAR, CONCAT and FM in the same select statement?

Question

I'm following up on a previous post that successfully generated an Oracle SELECT statement. From within that prior script I now need to

• concatenate two different fields (numeric values for 3-digit area codes and 7-digit phone numbers), then
• format the resulting column as XXX-XXX-XXXX

but my attempts at using TO_CHAR, CONCAT (or || I have tried doing the concatenation both ways), and FM in the same line result in invalid number or invalid operator errors (depending on how I've rearranged the elements in the line) painfully reminding me that my barely-elementary scripting shows a significant lack understanding of proper use and syntax.

The combination of TO_CHAR and CONCAT (||) successfully produces a 9-digit string, but I'm trying to attain as result formatted as XXX-XXX-XXXX from the following (I've edited out the lines from the original script for data elements not relevant to this particular question; nothing in the original query is nested, it just selects several fields and has a series of left joins linking on a common UID field in different tables)

select distinct
  cn.dflt_id StudentIdNumber,
  to_char (p.area_code || p.phone_no) Phone,
from
  co_name cn
  left join co_v_name_phone1 p on cn.name_id = p.name_id
order by cn.dflt_id

Would anyone offer helpful advice on attaining the desired XXX-XXX-XXXX formatting in the resulting Phone column? My attempts with variants of 'fm999g999g9999' have thus far not been successful.

Thanks,

Scott

Answer 1

Here are a few options that crossed my mind; have a look, pick the one you find the most appropriate. If you still have problems, post your own test case.

• RES2 is a simple concatenation of substrings that have a - in between
• RES3 uses format mask with an adjusted NLS_NUMERIC_CHARACTERS for thousands
• RES4 concatenates area code (which is OK by itself) with regular expression that splits a string into two parts; the first has {3} characters, and the second one has {4} of them

By the way, are area codes really numbers? No leading zeros?

---

SQL> with test (area_code, phone_number) as
  2    (select 123, 9884556 from dual union
  3     select 324, 1254789 from dual
  4    )
  5  select
  6    to_char(area_code) || to_char(phone_number) l_concat,
  7    --
  8    substr(to_char(area_code) || to_char(phone_number), 1, 3) ||'-'||
  9    substr(to_char(area_code) || to_char(phone_number), 4, 3) ||'-'||
 10    substr(to_char(area_code) || to_char(phone_number), 7)
 11    res2,
 12    --
 13    to_char(to_char(area_code) || to_char(phone_number),
 14            '000g000g0000', 'nls_numeric_characters=.-') res3,
 15    --
 16    to_char(area_code) ||'-'||
 17    regexp_replace(to_char(phone_number), '(\d{3})(\d{4})', '\1-\2') res4
 18  from test;

L_CONCAT      RES2          RES3          RES4
------------- ------------- ------------- -------------
1239884556    123-988-4556   123-988-4556 123-988-4556
3241254789    324-125-4789   324-125-4789 324-125-4789

SQL>