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

oracle, string-formatting, to-char

Solution

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.

Problem

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?

Original source