Solved

Remove Hyphens in Oracle SQL

Posted on 2016-09-09

2 solutions

175 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?

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

Join The Community

5 Comments

LVL 32

Assisted Solution

by:awking00

awking00 earned 250 total points

ID: 417915942016-09-09

select replace(ssn,"-",null)

LVL 32

Expert Comment

by:awking00

ID: 417916602016-09-09

Depending on your version, I believe you can also just do replace(ssn,'-')

LVL 74

Accepted Solution

by:sdstuber

sdstuber earned 250 total points

ID: 417916632016-09-09

if it might have other non-numeric characters (spaces, commas, periods, hyphens, etc.)

then try

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

LVL 32

Expert Comment

by:awking00

ID: 417916782016-09-09

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.

LVL 74

Expert Comment

by:sdstuber

ID: 417916822016-09-09

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]')

Featured Post

Question has a verified solution.

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

The strange case of the insufficient privileges error when creating a materialized view using execute immediate inside a PL/SQL procedure.

Article by: Javier

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…

Oracle Database

Downloading Oracle Log Files in AWS RDS

Article by: Doug

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface. I made a script to download the Alert Log, look for errors, and email me the trace files. In this article I'll describe what I did and share my script.