TRIM, REPLACE, STR, or Use a Shotgun to the SSN field???

Question

Post reply

TRIM, REPLACE, STR, or Use a Shotgun to the SSN field???

SQL_Enthusiast

Hall of Fame

Points: 3805
More actions
March 5, 2012 at 7:34 pm

#265537

I need to write a report for HR. The report needs a whole lot of data including the last four of the SSN. However, they have thrown me a curve ball. They want the SSN to be displayed as XXX-XX-0000 (0000 = last four digits. I can get the last four, but when I use the replace function (REPLACE (P.SSN, 7, 'XXX-XX-'), it only replaces the number 7 with the X's. How do I drop the left 7, replace with XXX-XX-, then right the last four?
Thank you

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQL_Enthusiast Hall of Fame Points: 3805 More actions · Answer 1

Never mind. I used a variable plus RIGHT

EXAMPLE:

DECLARE @mask VARCHAR(30)

SET @mask = 'XXX-XX-'

SELECT

p.FIRST_NAME + ' ' + p.LAST_NAME,

(SELECT @mask + RIGHT(p.SSN,4)) AS SSN

FROM

PERSON AS p

SQL_Enthusiast Hall of Fame Points: 3805 More actions · Answer 2

I'm not doing anything on the backend (and sorry if I lead you to believe this). HR is requesting a report from me. I'm trying to mask the SSN on the report I dump to excel for them. We store the full SSN in the database, I want the report to show them XXX-XX-0000.

drew.allen SSC Guru Points: 76986 More actions · Answer 3

Another option is STUFF()

STUFF(p.SSN, 1, 7, 'XXX-XX-'

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Possinator Hall of Fame Points: 3422 More actions · Answer 4

SQL_Enthusiast-AZ (3/5/2012)
Never mind. I used a variable plus RIGHT
EXAMPLE:
DECLARE @mask VARCHAR(30)
SET @mask = 'XXX-XX-'
SELECT
p.FIRST_NAME + ' ' + p.LAST_NAME,
(SELECT @mask + RIGHT(p.SSN,4)) AS SSN
FROM
PERSON AS p

or without the variable:

select 'xxx-xx-' + right(p.ssn,4) as SSN

[font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

SQL_Enthusiast Hall of Fame Points: 3805 More actions · Answer 5

SQL_Enthusiast

Hall of Fame

Points: 3805

March 6, 2012 at 5:06 pm

#1455676

Thank you. That's a lot easier...!

Possinator Hall of Fame Points: 3422 More actions · Answer 6

easy = good !

😀

[font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

CELKO (3/5/2012)
Why are you formatting data in the back end? The basic principle of a tiered architecture is that display is done in the front end and never in the back end. This is a more basic programming principle than just SQL and RDBMS.

If you don't do such a thing on the backend, then you're transmitting clear text SSN's over the wire where anyone with a smart phone can intercept them. 😉

The truth is, they shouldn't be storing the SSN in clear text to being with but that's a whole 'nuther story.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

SQL_Enthusiast-AZ (3/5/2012)
I need to write a report for HR. The report needs a whole lot of data including the last four of the SSN. However, they have thrown me a curve ball. They want the SSN to be displayed as XXX-XX-0000 (0000 = last four digits. I can get the last four, but when I use the replace function (REPLACE (P.SSN, 7, 'XXX-XX-'), it only replaces the number 7 with the X's. How do I drop the left 7, replace with XXX-XX-, then right the last four?
Thank you

Now that you have your answer, you should advise someone that they should not be storing SSN's in clear text to begin with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)