oracle 如何将数字格式化为 xxx-xx-xxxx？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1041415/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I format a number as xxx-xx-xxxx?
提问by akf
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?
如何在 Oracle 中像 xxx-xx-xxxx 一样格式化 xxxxxxxxx？
回答by Mark Harrison
SSN formatting with TO_CHAR
使用 TO_CHAR 格式化 SSN
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.
更新：感谢加里指出应该使用“0”格式字符而不是“9”来保留前导零。
回答by akf
you could also use the concat operator ||, which might be more readable.
您还可以使用 concat operator ||，它可能更具可读性。
SUBSTR(data, 1, 3) ||'-'||SUBSTR(data, 4, 2)||'-'||SUBSTR(data, 6, 4)
回答by Rob van Wijk
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:
如果您想在应用格式之前检查数字是否包含 9 位数字，那么正则表达式可能会有所帮助：
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}$)','--') 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.
问候，罗布。
回答by Ropstah
CONCAT(CONCAT(CONCAT(CONCAT(SUBSTR(sspdata, 1, 3), '-'), SUBSTR(sspdata, 4, 2)), '-',) SUBSTR(sspdata, 6, 4))
