How can I format a number as xxx-xx-xxxx?

Question

I am querying social security number data from a stored procedure and I would like to format it as a social security number in my stored procedure.

How can I format xxxxxxxxx like xxx-xx-xxxx in Oracle?

Is it stored as a VARCHAR or a NUMBER. If the latter, how are you dealing with leading zeros. — Gary Myers, Jul 26 '09 at 01:59

Mark Harrison · Answer 1 · 2009-07-27T09:30:52.983

17

SSN formatting with TO_CHAR

SELECT TO_CHAR(012345678, '000g00g0000','nls_numeric_characters=.-') ssn from dual;

SSN
-----------
012-34-5678

update: thanks to Gary for pointing out that the '0' format character should be used rather than the '9' to preserve leading zeroes.

edited Jul 27 '09 at 09:30

answered Jun 25 '09 at 03:38

Mark Harrison

297,451
125
333
465

4

+1, Oracle SQL programmers should become familiar with the formatting tools Oracle provides. – DCookie Jun 25 '09 at 19:30
1

That fails if the nine digit form of the SSN has leading zeroes. select TO_CHAR(23456789, 'fm000g00g0000','nls_numeric_characters=.-') ssn from dual; – Gary Myers Jul 26 '09 at 02:08

score 10 · Answer 2 · answered Jun 24 '09 at 23:20

10

you could also use the concat operator ||, which might be more readable.

 SUBSTR(data, 1, 3) ||'-'||SUBSTR(data, 4, 2)||'-'||SUBSTR(data, 6, 4)

answered Jun 24 '09 at 23:20

akf

38,619
8
86
96

Looks good and works if the data is character. If it is integer, then 7/8 digits (ie with leading 0 in the 9 digit form) would convert incorrectly – Gary Myers Jul 26 '09 at 02:11
I have a hard time believing this will ever be more readable than the to_char solution. Besides which, if the value to be formatted is the result of an expression or the return value of a function, wouldn't this cause redundant calls/calculations? – Air Mar 27 '17 at 21:33

score 5 · Answer 3 · answered Jun 25 '09 at 09:09

And if you'd like to check if the number consists of 9 digits before applying the format, then regular expressions can be of help:

SQL> create table t (nr)
  2  as
  3  select 123456789 from dual union all
  4  select 987654321 from dual union all
  5  select null from dual union all
  6  select 1234567 from dual union all
  7  select 12345678901234 from dual
  8  /

Tabel is aangemaakt.

SQL> select nr
  2       , regexp_replace(nr,'(^[[:digit:]]{3})([[:digit:]]{2})([[:digit:]]{4}$)','\1-\2-\3') formatted_nr
  3    from t
  4  /

                                    NR FORMATTED_NR
-------------------------------------- --------------------
                             123456789 123-45-6789
                             987654321 987-65-4321

                               1234567 1234567
                        12345678901234 12345678901234

5 rijen zijn geselecteerd.

Regards, Rob.

nice example of creating a demo table and also regular expressions! — Mark Harrison, Jun 27 '09 at 01:02

score 0 · Answer 4 · answered Jun 24 '09 at 23:03

0

CONCAT(CONCAT(CONCAT(CONCAT(SUBSTR(sspdata, 1, 3), '-'), SUBSTR(sspdata, 4, 2)), '-',) SUBSTR(sspdata, 6, 4))

answered Jun 24 '09 at 23:03

Ropstah

17,538
24
120
194

2

And there you have why I hate formatting stuff in most SQL dialects. Heinous! – Chris Jun 24 '09 at 23:19
I'm 4 years late to the party, but is there a good reason to use `CONCAT` over `||` ? – Phillip Jul 02 '14 at 15:38

score 0 · Answer 5 · answered Jul 24 '20 at 18:56

I'm only 11 years late, but I came to this question a few months ago and just figured out my new preferred method:

replace(to_char(my_number, 'FM000,00,0000'), ',', '-')

The FM trims the leading space, and the replace() turns those commas into dashes. One caveat: This only works for numbers (which is fine in our use-cases).

It's more elegant than substr() and more compact (and easier to remember, in my opinion) than nls_numeric_characters method.

That said, I assume this method is less efficient than the nls_numeric_characters method.

How can I format a number as xxx-xx-xxxx?

5 Answers5

Linked