Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

Remove Hyphens in Oracle SQL

Posted on 2016-09-09

2 solutions

Medium Priority

313 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 1000 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 1000 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

SQL Server - Converting RFC822 Dates Into Any Timezone

Article by: dsacker

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at: ht…

Export Table Data in SQL Server Management Studio

Article by: Dirk

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.

Copy Users and their Permissions between Databases

Video by: Steve

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.