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;