Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SSN Format in Oracle

Posted on 2016-09-15
2
Medium Priority
?
237 Views
Last Modified: 2016-09-21
I'm using the following code to format the ssn#s :
to_char(replace(emp.fica_nbr,'-'),999999999) SSNBR

The only issue is if a ssn# starts with 0, it is being omitted.
How can I adjust the code to capture all numbers, even if the leading one is a 0?
0
Comment
Question by:metalteck
  • 2
2 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 41800541
What is the TO_CHAR for?  It is already a character field.  You just need to remove the -

replace(emp.fica_nbr,'-') SSNBR
0
 
LVL 35

Accepted Solution

by:
johnsone earned 2000 total points
ID: 41800545
If you insist on the TO_CHAR, then this would do it, but you are doing a lot of unnecessary conversion.

to_char(to_number(replace(emp.fica_nbr,'-')),'000000000')
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question