Thanks for the question, Alex.
Asked: November 07, 2017 - 3:27 am UTC
Last updated: November 07, 2017 - 11:12 am UTC
Version: Oracle 12c
Viewed 10K+ times! This question is 
You Asked
I am querying a field 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?
I know I can do :
SELECT TO_CHAR(012345678, '000g00g0000','nls_numeric_characters=.-') ssn from dual;
or
SUBSTR(data, 1, 3) || '-' ||SUBSTR(data, 4, 2) || '-' || SUBSTR(data, 6, 4);
But my biggest concern is that the field may have many ssn separate by commas. For example :
1) 012345678
2) 012345678,876543210
3) 123456789,987654321,234567890
4) ...
and Chris said...
There are various ways you can do this. For example, you could split comma separated numbers out into rows. Then you can apply the techniques above.
But if you want to keep everything on the same row, you could use regular expressions. Wrap parentheses around expressions to find the N characters. Then use backreferences in the replacement separated by hyphens. e.g.:
with rws as (
select '123456789,987654321,234567890' str from dual union all
select '012345678,876543210' str from dual
)
select regexp_replace(str, '([0-9]{3})([0-9]{2})([0-9]{4})', '\1-\2-\3') fmt
from rws;
FMT
123-45-6789,987-65-4321,234-56-7890
012-34-5678,876-54-3210
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment