Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER


Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE	11.2.0.2.0	Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production


create table test_table(unmask varchar(20), mask varchar(20));
insert into test_table values('123', '897609');
1 rows affected

insert into test_table values('00123','896509');
1 rows affected

insert into test_table values('0000456','5632789');
1 rows affected

insert into test_table values('456','32567889');
1 rows affected

insert into test_table values('5678','5632789');
1 rows affected

select * 
from test_table;


UNMASK
MASK


123
897609

00123
896509

0000456
5632789

456
32567889

5678
5632789


select cast(unmask as number) as unmask_num, unmask, mask
from test_table;


UNMASK_NUM
UNMASK
MASK


123
123
897609

123
00123
896509

456
0000456
5632789

456
456
32567889

5678
5678
5632789


with duplicates as 
(
select cast(unmask as number) as unmask_num, count(*) as cnt
from test_table
group by cast(unmask as number)
)
select *
from duplicates
join test_table on duplicates.unmask_num = cast(unmask as number)
where cnt>1;


UNMASK_NUM
CNT
UNMASK
MASK


123
2
123
897609

123
2
00123
896509

456
2
0000456
5632789

456
2
456
32567889


with duplicates as 
(
select cast(unmask as number) as unmask_num, count(*) as cnt
from test_table
group by cast(unmask as number)
having count(*)>1
)
select *
from duplicates
join test_table on duplicates.unmask_num = cast(unmask as number)
;


UNMASK_NUM
CNT
UNMASK
MASK


123
2
123
897609

123
2
00123
896509

456
2
0000456
5632789

456
2
456
32567889

UNMASK_NUM	UNMASK	MASK
123	123	897609
123	00123	896509
456	0000456	5632789
456	456	32567889
5678	5678	5632789