Search code examples
sqlpostgresqltype-conversionsqldatatypes

Postgresql to_number() function format

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?

Solution

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