CODEWITHSUNDEEP
oracle-database
robbie
robbie

Reputation:

best way to remove '-' in oracle SP

I have an Oracle stored procedure, that has as one of its parameters a Social Security Number (SSN) in the hyphenated format xxx-xx-xxxx. What is the best way to remove the hyphens, so that I transform the SSN to a xxxxxxxxx format?

Upvotes: 1

Views: 12737

Answers (3)

Adrian Lynch
Adrian Lynch

Reputation: 8494

REPLACE('xxx-xx-xxxx', '-', '')

Or as @jitter mentions, the third argument defaults to '', so:

REPLACE('xxx-xx-xxxx', '-')

Upvotes: 8

Priyank
Priyank

Reputation: 14387

mystring := v_mySSN; select substr(mystring, 0, 3)||'-'||substr(mystring, 3, 2)||'-'||substr(mystring, 5, 4) into varialble from some_table;

This would work inside a procedure to select your value in a variable correctly.

Upvotes: 0

Adrian Lynch
Adrian Lynch

Reputation: 8494

To answer your second question, if you already have the xxx-xx-xxxx version, don't overwrite it and you'll have both.

If you're expecting xxxxxxxxx and you want xxx-xx-xxxx, piece it together using:

SUBSTR('xxxxxxxxx', 0, 3) || '-' || SUBSTR('xxxxxxxxx', 3, 2) || '-' || SUBSTR('xxxxxxxxx', 5, 4)

Upvotes: 1

Related Questions