How to center align a decimal value(including negative numbers) with 20 characters as total fixed length.
For ex: -234567.89 should be displayed as :
' -234567.89 '
There's no built-in function for this, a common way is nesting LPAD/RPAD:
RPAD(LPAD(TRIM(col),(20 - CHAR_LENGTH(TRIM(col))) / 2 + CHAR_LENGTH(TRIM(col))), 20)
But RPAD returns a VarChar of the maximum size (32000/64000), so you probably have to add a CAST to avoid the "row size exceeds limit" error:
CAST(RPAD(LPAD(TRIM(col),(20 - CHAR_LENGTH(TRIM(col))) / 2 + CHAR_LENGTH(TRIM(col))), 20) AS CHAR(20))
Using SUBSTRING instead of CAST you could create a SQL-UDF and pass the length as a parameter...
Thanks dieter, your expression worked for me, since I have decimal field (for ex COL1 which holds negative value), first I had to format it then, cast to varchar. After converting to varchar, I am getting spaces between the minus sign and value, to fix it, I had to use OREPLACE. Then I have used your expression to center align the column.
(RPAD(LPAD(TRIM(COL1),(20 - CHAR_LENGTH(TRIM(COL1))) / 2 + CHAR_LENGTH(TRIM(COL1))), 20) AS CHAR(20)) COL1
CAST( ( COL1 (FORMAT '-z(15)9.99')) AS VARCHAR(20))
no need for oTranslate, but your format defines leading blanks, simply change it:
CAST(( x (FORMAT '-(16)9.99')) AS VARCHAR(20))
TRIM( x (FORMAT '-(16)9.99'))