Derived Field for last 4 digits of the SSN

ctreports · Oct 10, 2006

I am trying to create a field in my report that takes the employee's social security number and makes it like this: xxx-xx-1234.

I thought using derived fields would be the way to go but I can't figure out which of the string functions to use and how to word it...

Can anyone help me? Thank you.

CharlesCook · Oct 11, 2006

It depends on the database brand you are using. What you want to do is string concatenation.

Try this:

'xxx-xx-' || SUBSTR(PERSONAL_DATA.SSN,5,4)

OR

CONCAT('xxx-xx-',SUBSTR(PERSONAL_DATA.SSN,5,4))

Make sure you double click in the data field name from the list of fields in the derived field editor. (Don't just type in PERSONAL_DATA.SSN as the syntax may not be correct)

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

ctreports · Oct 11, 2006

You are a genius. Your first example worked beautifully. Thanks a million!!!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Derived Field for last 4 digits of the SSN

ctreports

Technical User

CharlesCook

Programmer

ctreports

Technical User

Similar threads

Part and Inventory Search

Sponsor