467,134 Members | 924 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,134 developers. It's quick & easy.

casting and padding problem

hello,

update trl_info
set rec_count = repeat('0', 11- Length(cast((select count(*) from
tableA) as char(5))))
concat cast((select count(*) from tableA) as char(5))

The problem is the that value of count(*) may be big or smaller than 5
characters long so how to dynamically change the casting of the
interger to a char that is the same length of the count(*) size so
that the right of amount of leading '0's will be added for a total
character length of 11.

Thanks

WofD
Nov 12 '05 #1
  • viewed: 5856
Share:
3 Replies
I have no idea how to dynamically change the casting but I got the
result (UDB 8.1 FP7) you want with:

substr(right('00000000000'||digits(count(*)),11),1 ,11)

1. "count(*)" - Count the number of rows
2. "digits" - Convert to a string with leading zeros
3. "||" - Concatenate 11 zeros onto the front of the result
4. "right" - Use only the rightmost 11 characters
(The following is needed for command line usage to truncate the
generated string. You may need it too.)
5. "substr" - Use only the first 11 characters of the string

This also has the advantage of performing the count only once. I leave
it to you to code this as a "case" statement to insert the correct
number of leading zeros.

Phil Sherman
Thanos wrote:
hello,

update trl_info
set rec_count = repeat('0', 11- Length(cast((select count(*) from
tableA) as char(5))))
concat cast((select count(*) from tableA) as char(5))

The problem is the that value of count(*) may be big or smaller than 5
characters long so how to dynamically change the casting of the
interger to a char that is the same length of the count(*) size so
that the right of amount of leading '0's will be added for a total
character length of 11.

Thanks

WofD


Nov 12 '05 #2
would this do the trick??

update trl_info
set rec_count = repeat('0', 11- Length(ltrim((cast((select count(*) from
tableA) as char(5))))))
concat ltrim(cast((select count(*) from tableA) as char(11)))

in******@hotmail.com (Thanos) wrote in message news:<ea**************************@posting.google. com>...
hello,

update trl_info
set rec_count = repeat('0', 11- Length(cast((select count(*) from
tableA) as char(5))))
concat cast((select count(*) from tableA) as char(5))

The problem is the that value of count(*) may be big or smaller than 5
characters long so how to dynamically change the casting of the
interger to a char that is the same length of the count(*) size so
that the right of amount of leading '0's will be added for a total
character length of 11.

Thanks

WofD

Nov 12 '05 #3
How about this?
update trl_info
set rec_count = DIGITS(CAST((select count(*) from tableA) AS INTEGER))
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Christopher Benson-Manica | last post: by
20 posts views Thread by j0mbolar | last post: by
12 posts views Thread by T Koster | last post: by
19 posts views Thread by Ramesh Tharma | last post: by
17 posts views Thread by goldfita@signalsguru.net | last post: by
36 posts views Thread by phil-news-nospam@ipal.net | last post: by
33 posts views Thread by Mark P | last post: by
7 posts views Thread by William S Fulton | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.