Home » SQL & PL/SQL » SQL & PL/SQL » need to show last 4 digits
need to show last 4 digits [message #445950] Thu, 04 March 2010 20:31 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
I have a requirement where I have a value stored in varchar2(12), and no matter what the size of data, I should mask all the characters except the last 4

eg: - 23115645

after masking should be - xxxx5645,

create table test1(x varchar2(12))


insert into test1 values('34567745')
/
insert into test1 values('438765311')
/
insert into test1 values('1112345')
/
commit

alter table test1 add(y varchar2(12))

update test1 a
set a.y = replace(x, substr(x,1,5),'xxxx') 
/
commit





y should store value of x in such a way that no matter what the size of x is (7 characters or 9), we should see only the last 4, but the remaining should be masked with xxxx

I tried the above update, but using substr, i am able to get this if x has 9 characters

not sure how to achieve this if x has 7, or 4 characters
Re: need to show last 4 digits [message #445951 is a reply to message #445950] Thu, 04 March 2010 20:32 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
use LENGTH() function
Re: need to show last 4 digits [message #445956 is a reply to message #445950] Thu, 04 March 2010 21:50 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member

select x, rpad('9',length(x)-4,'9')||substr(x,-4) res from test1
Re: need to show last 4 digits [message #446231 is a reply to message #445950] Sat, 06 March 2010 15:17 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
The fix I have posted in the previous message may not work for data in certain format...


the data varies from either a single '0' to something like what I mentioned below, I need to show the last 4 digits, and the remaining should be masked by 9...Not sure how to eliminate
spaces and hypens

I tried this query but it does not work if there are less than four digits in the data in which case it returns null

select x, rpad('9',length(x)-4,'9')||substr(x,-4) res from gtst



create table gtst(x varchar2(50))
/

insert into gtst values ('4221245') -- result should be 9991245
/
insert into gtst values ('0') -- result should be 999999990
/
insert into gtst values ('12') -- result should be 999999912
/
insert into gtst values ('123') -- result should be 999999123
/
insert into gtst values ('123456888') -- result should be 999996888
/
insert into gtst values ('12345') -- result should be 999992345
/
insert into gtst values ('432 98 1399') -- note we have space in between, result should be 999991399
/
insert into gtst values ('436-74-3035') -- note there is hypen in between, result should be 999993035
/
insert into gtst values ('1234') -- result should be 999991234
/
commit




Re: need to show last 4 digits [message #446232 is a reply to message #445950] Sat, 06 March 2010 15:34 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
this is the closest i could get so far

select x,nvl(rpad('9',length(x)-4,'9'),rpad('9',length(x),'9'))||substr(x,-4) from gtst 
Re: need to show last 4 digits [message #446233 is a reply to message #446231] Sat, 06 March 2010 15:37 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
What should be returned from the following

insert into gtst values ('A1B2C3D4E5F6G7H8I9J0K1L2M3N4O5P6Q7R8S9T0U1V2W3X4Y5');
insert into gtst values ('`~1!2@3#4$5%6^7&8*9(0)-1=2+3_1');
Re: need to show last 4 digits [message #446234 is a reply to message #445950] Sat, 06 March 2010 15:45 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
if there is any such data (any text, letters etc), then result shoud be 999999999 (nine 9s)
Re: need to show last 4 digits [message #446235 is a reply to message #446231] Sat, 06 March 2010 16:03 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Not sure how to eliminate spaces and hypens

SQL> @a1
SQL> select translate('432 98 1399','1234567890 ','1234567890') from dual
  2  /

TRANSLATE
---------
432981399

SQL> select translate('432-98-1399','1234567890-','1234567890') from dual
  2  /

TRANSLATE
---------
432981399
Re: need to show last 4 digits [message #446236 is a reply to message #445950] Sat, 06 March 2010 17:14 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
thanks
Re: need to show last 4 digits [message #446251 is a reply to message #445950] Sun, 07 March 2010 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select x,
  2         lpad(regexp_replace(translate(x,'0 -','0'),'^.*[^[:digit:]].*$','999999999'),9,'9') newx
  3  from gtst
  4  /
X                                                  NEWX
-------------------------------------------------- ---------
4221245                                            994221245
0                                                  999999990
12                                                 999999912
123                                                999999123
123456888                                          123456888
12345                                              999912345
432 98 1399                                        432981399
436-74-3035                                        436743035
1234                                               999991234
A1B2C3D4E5F6G7H8I9J0K1L2M3N4O5P6Q7R8S9T0U1V2W3X4Y5 999999999
`~1!2@3#4$5%6^78*9(0)-1=2+3_1                      999999999
`~1!2@3#4$5%6^7&8*9(0)-1=2+3_1                     999999999

12 rows selected.

Regards
Michel
Re: need to show last 4 digits [message #446253 is a reply to message #445950] Sun, 07 March 2010 03:05 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
after Michel's transformation

SELECT x, newx,
       REGEXP_REPLACE (newx,
                       '([url=http://www.orafaq.com/wiki/:digit:]:digit:[/url]{5})([url=http://www.orafaq.com/wiki/:digit:]:digit:[/url]{4})',
                       '99999\2'
                      ) newx2
  FROM (SELECT x,
               LPAD (REGEXP_REPLACE (TRANSLATE (x, '0 -', '0'),
                                     '^.*[^[:digit:]].*$',
                                     '999999999'
                                    ),
                     9,
                     '9'
                    ) newx
          FROM gtst) gtst2


sorry about format, but "[:digit:]" show as "url=http://www.orafaq.com/wiki/:digit:]:digit:[/url"

change "url=http://www.orafaq.com/wiki/:digit:]:digit:[/url" to "[:digit:]"

[Updated on: Sun, 07 March 2010 03:20]

Report message to a moderator

Re: need to show last 4 digits [message #446257 is a reply to message #445950] Sun, 07 March 2010 03:43 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
for Perl-Influenced Extensions in Oracle Regular Expressions
SELECT x, newx,
       REGEXP_REPLACE (newx,
                       '(\d{5})(\d{4})',
                       '99999\2'
                      ) newx2
  FROM (SELECT x,
               LPAD (REGEXP_REPLACE (TRANSLATE (x, '0 -', '0'),
                                     '^.*[^[:digit:]].*$',
                                     '999999999'
                                    ),
                     9,
                     '9'
                    ) newx
          FROM gtst) gtst2
Re: need to show last 4 digits [message #446259 is a reply to message #446257] Sun, 07 March 2010 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I hope you have fun but what is the purpose of these queries?

Regards
Michel
Re: need to show last 4 digits [message #446261 is a reply to message #445950] Sun, 07 March 2010 04:46 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
SQL> SELECT x, newx,
  2         REGEXP_REPLACE (newx,
  3                         '(\d{5})(\d{4})',
  4                         '99999\2'
  5                        ) newx2
  6    FROM (SELECT x,
  7                 LPAD (REGEXP_REPLACE (TRANSLATE (x, '0 -', '0'),
  8                                       '^.*[^[:digit:]].*$',
  9                                       '999999999'
 10                                      ),
 11                       9,
 12                       '9'
 13                      ) newx
 14            FROM gtst) gtst2
 15  /

X                                                            NEWX                 NEWX2
------------------------------------------------------------ -------------------- ------------------
4221245                                                      994221245            999991245
0                                                            999999990            999999990
12                                                           999999912            999999912
123                                                          999999123            999999123
123456888                                                    123456888            999996888
12345                                                        999912345            999992345
432 98 1399                                                  432981399            999991399
436-74-3035                                                  436743035            999993035
1234                                                         999991234            999991234
A1B2C3D4E5F6G7H8I9J0K1L2M3N4O5P6Q7R8S9T0U1V2W3X4Y5           999999999            999999999
`~1!2@3#4$5%6^7&8*9(0)-1=2+3_1                               999999999            999999999

11 rows selected.
Re: need to show last 4 digits [message #446262 is a reply to message #446261] Sun, 07 March 2010 04:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you take my query output as input and show that what you add does not change anything.
You could just do
select x, newx, newx newx2 from myquery
but you can also find many more complex ways to achieve the same.

Regards
Michel
Re: need to show last 4 digits [message #446273 is a reply to message #445950] Sun, 07 March 2010 07:17 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
if you look more closely you will see difference.

what else Michel Cadot?

why are you aggressive?

Re: need to show last 4 digits [message #446284 is a reply to message #446251] Sun, 07 March 2010 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select x,
  2         '99999' ||
  3         substr(lpad(regexp_replace(translate(x,'0 -','0'),'^.*[^[:digit:]].*$','999999999'),9,'9'),-4)
  4           newx
  5  from gtst
  6  /
X                                                  NEWX
-------------------------------------------------- ---------
4221245                                            999991245
0                                                  999999990
12                                                 999999912
123                                                999999123
123456888                                          999996888
12345                                              999992345
432 98 1399                                        999991399
436-74-3035                                        999993035
1234                                               999991234
A1B2C3D4E5F6G7H8I9J0K1L2M3N4O5P6Q7R8S9T0U1V2W3X4Y5 999999999
`~1!2@3#4$5%6^78*9(0)-1=2+3_1                      999999999
`~1!2@3#4$5%6^7&8*9(0)-1=2+3_1                     999999999

12 rows selected.

Regards
Michel
Re: need to show last 4 digits [message #446290 is a reply to message #445950] Sun, 07 March 2010 09:35 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
you saw difference. very good Smug

Quote:

what else Michel Cadot?

why are you aggressive?
Re: need to show last 4 digits [message #446303 is a reply to message #446290] Sun, 07 March 2010 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't see the difference I reread OP's question.
I'm aggressive because I asked you twice something and you didn't (/refused to?) answer.

Regards
Michel
Re: need to show last 4 digits [message #446304 is a reply to message #445950] Sun, 07 March 2010 13:48 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
the difference what i want to underline:
NEWX      NEWX2
--------- ----------
994221245 999991245  (difference)
999999990 999999990
999999912 999999912
999999123 999999123
123456888 999996888  (difference)
999912345 999992345  (difference)
432981399 999991399  (difference)
436743035 999993035  (difference)
999991234 999991234
999999999 999999999
999999999 999999999

why you post this if you don't see the difference above?
Quote:
SQL> select x,
2 '99999' ||
3 substr(lpad(regexp_replace(translate(x,'0 -','0'),'^.*[^[:digit:]].*$','999999999'),9,'9'),-4)
4 newx
5 from gtst
6 /


Quote:
I'm aggressive because I asked you twice something and you didn't (/refused to?) answer.

what did you ask? this? twice? Shocked
Quote:
I hope you have fun but what is the purpose of these queries?

if yes then purpose is help to ora1980

may be you aggressive because i used you query?
SORRY for using you query


Re: need to show last 4 digits [message #446306 is a reply to message #446304] Sun, 07 March 2010 14:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
why you post this if you don't see the difference above?

I already say it, because I reread OP's question.

Quote:
if yes then purpose is help to ora1980

Too late!

Quote:
may be you aggressive because i used you query?

If I didn't want others use my queries I should not post them.
But I'd prefer you improve it. I think it can be done in a better shorter way but I have no time (and no longing to) to search for this, as you took time to add an additional step you could spend time to rewrite it in a better way.

Regards
Michel
Re: need to show last 4 digits [message #446311 is a reply to message #445950] Sun, 07 March 2010 17:35 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
Michel Cadot, if you have any claims, please, send their to e-mail and i will more briefly.

let's close a debate

thanks
Re: need to show last 4 digits [message #446350 is a reply to message #446311] Sun, 07 March 2010 23:27 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The case was closed for me since before my last query, YOU resarted it posting me question (and in the opposite to you, I answer the questions): http://www.orafaq.com/forum/mv/msg/155659/446290/102589/#msg_446290

Regards
Michel

[Updated on: Sun, 07 March 2010 23:28]

Report message to a moderator

Previous Topic: date problem
Next Topic: ORA-06503
Goto Forum:
  


Current Time: Sat Dec 03 08:15:53 CST 2016

Total time taken to generate the page: 0.18960 seconds