By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,226 Members | 1,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,226 IT Pros & Developers. It's quick & easy.

casting and padding problem

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.