clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601479 fiddles created (47957 in the last week).

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
 hidden batch(es)


create table test_table(unmask varchar(20), mask varchar(20));
 hidden batch(es)


insert into test_table values('123', '897609');
1 rows affected
 hidden batch(es)


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


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


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


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


select * from test_table;
UNMASK MASK
123 897609
00123 896509
0000456 5632789
456 32567889
5678 5632789
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)