Solved

Remove Hyphens in Oracle SQL

Posted on 2016-09-09
5
43 Views
Last Modified: 2016-09-09
I have a ssn field.
The field has hyphens in it.
Ex. xxx-xx-xxxx

How can I just extract the numbers without the hyphens?
0
Comment
Question by:metalteck
  • 3
  • 2
5 Comments
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 250 total points
Comment Utility
select replace(ssn,"-",null)
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Depending on your version, I believe you can also just do replace(ssn,'-')
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
Comment Utility
if it might have other non-numeric characters (spaces, commas, periods, hyphens, etc.)

then try  

regexp_replace(ssn,'[^0-9]')
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
It's quite common to express ssn in the xxx-xx-xxxx format and I suspect eliminating the hyphens may be all that's needed. There is also something called a PTIN (stands for preparer tin) that begins with the letter "P" which wouldn't be retained if just the digits are retrieved.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
if you need to preserve a "P" as well as digits and exclude all others then simply add P to the "keep" list.

regexp_replace(ssn,'[^0-9P]')
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now