Finding Duplicate Accounts

I’m facing a problem with the data I’ve because of duplicate accounts, and I need to find all the duplicate accounts. For example

Main_account_number: XXXXXX | 
Duplicate_account_number: 00000XXXXXX.

Found some duplicate account like this with 0’s as the prefix.

I tried framing an SQL using a subquery but that is not working

select acct_num,
       (select acct_num
        from account_table
        where acct_num like '0000%'+X.acct_num) as dup_acct
from account_table as X;

expected:

acct_num|dup_acct
123455  |0000123455
455585  |000045585

Answer

Is this what you want?

select a.acct_num, a2.acct_num as duplicate_acct_num
from account_table a join
     account_table a2
     on a2.acct_num = concat('0000', a.acct_num);

Or, if the number of 0s can vary:

     on a2.acct_num regexp concat('^0+',  a.acct_num, '$');