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?
Or shorter REPLACE(yourstringhere, '-')
jitter
2009-06-24 22:23:59
thanks! and what if I have xxxxxxxxx and I want to turn it into xxx-xx-xxxx?
2009-06-24 22:47:27
I cant use this in a SP?
2009-06-24 23:07:30
I am trying to use this in a procedure but get the following error: 'REPLACE' is not a procedure or is undefined
2009-06-24 23:10:41
REPLACE is a function eg new_val := REPLACE(old_val,'-');
Gary
2009-06-24 23:35:05
+1
A:
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)
Adrian Lynch
2009-06-25 00:20:12
A:
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.
Priyank
2009-06-26 13:44:35