How to convert integer to string and get length of string

Question

In my database there is a field last_id of type integer. The table contains several rows. Let's say the maximum value of last_id is 104050. Now I want to know the length of this integer.

As this is not possible I am trying to convert it into a string.

SELECT to_char(MAX(last_id),'99') FROM xxxx

I would expect this to yield 10 with type = text, but instead it returns ## type = text. Afterwards I would use SELECT char_length(to_char(MAX(last_id),'99')) FROM xxx which should return 2 ...

What is going wrong here?

Answer 1

Cast the integer value to text:

SELECT length(max(last_id)::text) FROM xxx;

Answer 2

Since you are dealing with integers you can actually find the length of the number (or number of digits in the number) directly. Mathematically, the number of digits in an integer is one more than the floor of the log base 10 of the number.

You could use the following to find the length directly:

SELECT FLOOR(LOG(MAX(last_id))) + 1 FROM xxxx

Answer 3

Don't work with postgresql, but after documentation read... Somthign like this.

SELECT character_length(cast(last_id as varchar(20))) from table