Derived Field for last 4 digits of the SSN

[ctreports]

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]

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]

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