Derived Field for last 4 digits of the SSN

Question

Derived Field for last 4 digits of the SSN

ctreports (TechnicalUser)

(OP)

10 Oct 06 16:55

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 (Programmer) · Answer 1 · 2006-10-11 08:32:50

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 (TechnicalUser) · Answer 2 · 2006-10-11 12:40:21

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

Come Join Us!

Posting Guidelines

Derived Field for last 4 digits of the SSN