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?

The reason is the "fill mode" of the to_char() function. Using to_char(last_id, 'FM99') would probably solve your problem. — user330315
– user330315, Commented Mar 18, 2014 at 10:40
Why would you accept rjhdby's answer? Mine is shorter, better, earlier and actually correct. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 19, 2014 at 11:08
@ErwinBrandstetter you're right, I have fixed this now as it helped solve my problem first place — coala
– coala, Commented Oct 16, 2015 at 22:34

Erwin Brandstetter · Accepted Answer · 2022-01-05 01:41:57Z

18

Cast the integer value to text:

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

edited Jan 5, 2022 at 1:41

answered Mar 18, 2014 at 10:36

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

coala Over a year ago

Great, that works perfectly. AS a next step I would like to to do the following: Add spaces to the field last_id so that the length of that field is 10 instead of 6. So if it only has a length of 5 I want to add 5 spaces to it, if it is 4 I want to add 6. Is that possible?

Erwin Brandstetter Over a year ago

@user3387124 Sure, pad right or left? But ask a new question please. This one was about finding out the length of an integer in text representation.

Stoddard · Accepted Answer · 2018-01-02 18:09:12Z

3

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

edited Jan 2, 2018 at 18:09

answered Jan 2, 2018 at 18:00

Stoddard

314 bronze badges

1 Comment

JosephDoggie Over a year ago

Needless to say, this only works with positive integers. Since in most systems, an id would be a positive integer, this wouldn't be a great limitation. SELECT FLOOR(LOG(MAX(abs(last_id) ))) + 1 FROM xxxx should always work for the numeric part, though one might want to add logic to increase the answer (in character-count) for negative numbers. Example: for -1000, as is, the formula I have in this comment would give 4 instead of 5, so the string conversion in others answers would handle this case better.

rjhdby · Accepted Answer · 2014-03-18 10:38:12Z

0

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

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

answered Mar 18, 2014 at 10:38

rjhdby

1,3971 gold badge13 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to convert integer to string and get length of string

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related