You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Update statement to change SSN(Social Security Number) no in PeopleSoft:

1 reply [Last post]
Guest's picture
Guest

Hi Folks,

Can anybody help me what will be the update statement for SSN no format change.I am giving you the example in below:
Existing SSN in PeopleSoft--- 830-75-6027.

SSN should be updated to 000-75-6027.

kindly help me to write the update statement on it.
Table name: PS_PERS_NID

Thanks in advance.

Lepa's picture
User offline. Last seen 2 years 1 week ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Update statement to change SSN(Social Security Number) ...

Are you sure you want to do such an update? This will completely over ride the # and not just mask it!

Anyway, you can use replace and substr oracle functions to do that.

Example to illustrate this:
SELECT REPLACE ('830-75-6027', SUBSTR ('830-75-6027', 1, 4), '000-')
FROM DUAL;

The actual update will look something like this:
UPDATE ps_pers_nid
SET ssn_field=
REPLACE (ssn_field, SUBSTR (ssn_field, 1, 4), '000-');

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog