4

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?

Improve this question

3 Answers 3

Reset to default
19

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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

Improve this answer

Comments

-1

use like this it is simple:

to_number(to_char(yournumber, '99'),'99')

Improve this answer

1 Comment

Code-only answers are discouraged because they do not explain how they resolve the issue. Please update your answer to explain how this improves on the accepted and upvoted answer this question already has. Please review How do I write a good answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.