On 10 Feb 2013 15:13, A Paul wrote:
Value will contain one digit or max two digits on the left side of
the decimal point. So it could be 1.2500000000 or 12.500000000, all
positive values only.
So the remaining question for varied numeric values of the "VALUE"
column was about the relevance of alignment in the outcome. If no
matter, then the given statement should suffice; i.e. values greater
than or equal to one but less than ten will align differently across
rows than either those values from zero to less than one, and those
values from ten to less than one hundred.
If alignment is important, and leading zeroes are acceptable, then
use of the DIGITS() as shown by the response from Lim Hock-Chai should
suffice.
If both alignment and no leading zeroes are required, then the either
a more complex expression is required for the concatenation of the
"VALUE" data to the string, or that complexity is best deferred to a UDF
to perform the right-alignment of the numeric being cast to character
[because the SQL provides no builtin scalar to do that :-( ].
Example rows with the smaller positive values:
DESCR1 VALUE
....+....1....+....2....+....3
XXX 01FEB 13 AXA INS LHRTD 12.50000000
XXX 01MAR 13 AXA INS LHRTD 1.25000000
XXYY 04MAR 13 AXA INS LHRTD 0.12500000
Effect using the expression I offered, the alignment is not fixed:
DESCR1 VALUE
....+....1....+....2....+....3
X 01FEB 13 AXA INS LHR 1250 12.50000000
X 01MAR 13 AXA INS LHR 125 1.25000000
X 04MAR 13 AXA INS LHR 12 .12500000
Effect using the expression using DIGITS, the alignment is fixed, but
the numbers have leading zeroes:
DESCR1 VALUE
....+....1....+....2....+....3
X 01FEB 13 AXA INS LHR 1250 12.50000000
X 01MAR 13 AXA INS LHR 0125 1.25000000
X 04MAR 13 AXA INS LHR 0012 .12500000
Or perhaps neither of those is satisfactory, and the following is the
desired effect?:
DESCR1 VALUE
....+....1....+....2....+....3
X 01FEB 13 AXA INS LHR 1250 12.50000000
X 01MAR 13 AXA INS LHR 125 1.25000000
X 04MAR 13 AXA INS LHR 12 .12500000
midrange.com
