Postgresql to_number() function format

Question

I want to use postgresql to_number(numberString, format_mask). My numberString may contain leading zeros (I don't know the actual length of these zeros and the numberString in total). I want to trim these zeros and get the value as number.

I've read about this function here and currently using the format:

select to_number('00005469', '9999999999')

But if the length of '9's is less than the length of the numberString then I can't get the correct number. How can I make this work without writing a long list of '9' in format_mask?

Answer 1

You don't need to_number() for this. Just cast the string to an integer:

select '00005469'::integer;

or using standard SQL:

select cast('00005469' as integer);

Answer 2

To be more safe if your column has decimals or not, use

 select  NULLIF('00005469', '')::decimal

This select '00005469'::integer; will not work if there are decimals