Home » SQL & PL/SQL » SQL & PL/SQL » Replacing First Five Digits in SSN
Replacing First Five Digits in SSN [message #416975] Wed, 05 August 2009 08:56 Go to next message
v1960u
Messages: 2
Registered: August 2009
Location: Boston
Junior Member
Hi SQL GURU's,

I am very new to programming and I have a request to create a report selecting data from HR warehouse table out putting personal data including SSN. but SSN display must be first five digits will show like 999992371. some body suggested use substr and replace function. I have tried but I am not able to successed. Please help me with this, I am learning little bit at a time.
select replace(substr(ssn,1,5)'xxxxx') from dbo.WH_SEC_USER

thanks,

varun

Report message to a moderator

Re: Replacing First Five Digits in SSN [message #416978 is a reply to message #416975] Wed, 05 August 2009 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It is not clear what you want to do.

Post a working Test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

Report message to a moderator

Re: Replacing First Five Digits in SSN [message #416983 is a reply to message #416975] Wed, 05 August 2009 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
posting same problem 5 times, won't get you an answer any faster!

Report message to a moderator

Re: Replacing First Five Digits in SSN [message #416985 is a reply to message #416975] Wed, 05 August 2009 09:14 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
v1960u wrote on Wed, 05 August 2009 15:56
Hi SQL GURU's,

I am very new to programming and I have a request to create a report selecting data from HR warehouse table out putting personal data including SSN. but SSN display must be first five digits will show like 999992371.

Firstly, this does not seem to be SQL problem, only the logical one.
Secondly, you should exactly formulate, what you want. Then it would be easy to find suitable Oracle functions for achieving it.

Based on the posted example, you may want:
- show '99999' instead of first 5 SSN digits - use constant string '99999'; if you want to show less number of '9's when SSN has less than 5 digits, you may use LPAD/RPAD combined with LENGTH to get required number of '9's
- concatenate it with the rest of SSN (from 6th digit to the end - use SUBSTR)

Report message to a moderator

Re: Replacing First Five Digits in SSN [message #416987 is a reply to message #416983] Wed, 05 August 2009 09:20 Go to previous messageGo to next message
v1960u
Messages: 2
Registered: August 2009
Location: Boston
Junior Member
I didn't post five times, some thing happened when I hit create topic. My computer was frozen probably I may hit enter multiple times.

Report message to a moderator

Re: Replacing First Five Digits in SSN [message #416995 is a reply to message #416987] Wed, 05 August 2009 09:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
probably I may hit enter multiple times.

Sure, next time FIRST go to forum topic list and THEN ONLY IF your topic is not there repost.

Now explain your question and follow has been posted.

Regads
Michel

Report message to a moderator

Re: Replacing First Five Digits in SSN [message #416999 is a reply to message #416995] Wed, 05 August 2009 10:12 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Michel Cadot wrote on Wed, 05 August 2009 16:56
Quote:
probably I may hit enter multiple times.

Sure, next time FIRST go to forum topic list and THEN ONLY IF your topic is not there repost.

That might prove to be a bit hard if your computer hangs.
Had a duplicate post myself the other day, whereas I am 100% clear that I sumitted only once, so let's not be too harsh on others.

Report message to a moderator

Re: Replacing First Five Digits in SSN [message #417000 is a reply to message #416995] Wed, 05 August 2009 10:14 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Michel Cadot wrote on Wed, 05 August 2009 16:56
Quote:
probably I may hit enter multiple times.

Sure, next time FIRST go to forum topic list and THEN ONLY IF your topic is not there repost.

That might prove to be a bit hard if your computer hangs.
Had a duplicate post myself the other day, whereas I am 100% clear that I sumitted only once, so let's not be too harsh on others.

Report message to a moderator

Re: Replacing First Five Digits in SSN [message #417002 is a reply to message #417000] Wed, 05 August 2009 10:24 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
LMAO, Nice one Smile

Report message to a moderator

Re: Replacing First Five Digits in SSN [message #417003 is a reply to message #417002] Wed, 05 August 2009 10:36 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
LOL...
Same thing happened again. No pun was intended.

Report message to a moderator

Re: Replacing First Five Digits in SSN [message #417005 is a reply to message #417000] Wed, 05 August 2009 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Frank wrote on Wed, 05 August 2009 17:14
Michel Cadot wrote on Wed, 05 August 2009 16:56
Quote:
probably I may hit enter multiple times.

Sure, next time FIRST go to forum topic list and THEN ONLY IF your topic is not there repost.

That might prove to be a bit hard if your computer hangs.
...

That will learn the patience instead of compulsively click on a button and will help not only here but on many applications (developed by some that post here).

Regards
Michel

Report message to a moderator

Re: Replacing First Five Digits in SSN [message #417007 is a reply to message #416975] Wed, 05 August 2009 12:04 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> some body suggested use substr and replace function
With free advice, you get what you paid for it.
REPLACE is not needed as part of the solution; only SUBSTR.

Report message to a moderator

Previous Topic: fast operation
Next Topic: ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 10 02:39:39 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.