1

I have a table that has got the below set of records.

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

From the above table I need to select only the below records.

  UNMASK    
123 
00123   
0000456 
456 

(I.E) Whatever values in UNMASK has any number of '0' before the '1-9' and also the row that doesn't have 0s before that 1-9.

Like 123 and there can be 000000123, 456 and 00456, I need such records(only Prefixed with 0s and the original value) in the output using SQL query in Oracle.

I tried the below query but it doesn't work the way I want.

Select UNMASK,TRIM (LEADING '0' FROM UNMASK) from HSA.TEST_TABLE group by UNMASK having count(TRIM(LEADING '0' FROM UNMASK)) > 1;
0

Test data:

create table test_table(unmask varchar(20), mask varchar(20));
insert into test_table values('123', '897609');
insert into test_table values('00123','896509');
insert into test_table values('0000456','5632789');
insert into test_table values('456','32567889');
insert into test_table values('5678','5632789');

I've done this with a CTE, as I love them. casting the string to a number removes the leading characters, which I find easier than messing around with TRIM.

Query:

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)
;

DB Fiddle that demonstrates is here.

0

You need to do it in 2 steps, first find "duplicates":

Select TRIM (LEADING '0' FROM UNMASK) 
from HSA.TEST_TABLE 
group by TRIM (LEADING '0' FROM UNMASK)  
having count(1) > 1;

From there you can join with the original table:

select tt.*
from HSA.TEST_TABLE tt
join (
    Select TRIM (LEADING '0' FROM UNMASK) as unmask 
    from HSA.TEST_TABLE 
    group by TRIM (LEADING '0' FROM UNMASK)  
    having count(1) > 1
) t
    on t.unmask = TRIM (LEADING '0' FROM tt.UNMASK) 

All untested since neither table ddl nor insert statements for sample data where availible

  • HI,It works. But what if the datatype of UNMASK column is VARCHAR2 and it also has alphabets. The above command wont work right? – sabarish jackson May 17 at 10:31
  • It just removes leading '0' from the string, so I assume it should work. – Lennart May 17 at 10:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.