Remove non-digits from collated string and only keep numbers

This should do it:

select lpad(regexp_replace(Account_Number_Column,'[^\\d]*'),10,0);

Of course, you may want to also incorporate other elements around data quality (like ensuring your non-padded result is <= 10 characters, etc). Whatever constitutes an acceptable result in your use case.

If you have collation, I would suggest removing it for the REGEXP function, and reapply to the resulting string.

Here's an example where if you had a collation of 'en-ci' you could strip it for the function and reapply for the result:

select collate(lpad(regexp_replace(collate(Account_Number,''),'[^\\d]*'),10,'0'),'en-ci');

One method is it use REGEXP_SUBSTR_ALL to get the array of matching digits, and then turn that array into a string with ARRAY_TO_STRING, which can then be LPAD to 10

SELECT Account_Number_Column
    ,ARRAY_TO_STRING(REGEXP_SUBSTR_ALL(Account_Number_Column, '\\d'),'') as result_a
    ,LPAD(result_a,10,'0') as answer_a
FROM VALUES
    ('00#9999999'),
    ('000123456M'),
    ('N/A')
    t(Account_Number_Column);

gives:

Striping the COLLATION off strings is normally bad, BUT given you only want digit's is should ok in this instance to do so via collate(Account_Number_Column,'utf8')

SELECT collate(raw, 'sp-upper') as Account_Number_Column
    ,lpad(regexp_replace(collate(Account_Number_Column,'utf8'),'[^\\d]*'),10,'0')
FROM VALUES
    ('00#9999999'),
    ('000123456M'),
    ('N/A')
    t(raw);

but it should be Jim's answer:

select lpad(regexp_replace(collate(Account_Number_Column,'utf8'),'[^\\d]*'),10,'0');