Solved

Remove Hyphens in Oracle SQL

Posted on 2016-09-09
5
115 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 41791594
select replace(ssn,"-",null)
0
 
LVL 32

Expert Comment

by:awking00
ID: 41791660
Depending on your version, I believe you can also just do replace(ssn,'-')
0
 
LVL 74

Accepted Solution

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

then try  

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

Expert Comment

by:awking00
ID: 41791678
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 74

Expert Comment

by:sdstuber
ID: 41791682
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

738 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