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  |
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 #446231 is a reply to message #445950] |
Sat, 06 March 2010 15:17  |
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 #446251 is a reply to message #445950] |
Sun, 07 March 2010 02:43 |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 |
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 #446261 is a reply to message #445950] |
Sun, 07 March 2010 04:46 |
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 #446284 is a reply to message #446251] |
Sun, 07 March 2010 08:51 |
|
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 13 04:47:05 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
