How to center align a decimal value(including negative numbers) with 20 characters as total fixed length

Database

How to center align a decimal value(including negative numbers) with 20 characters as total fixed length

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     '

Tags (1)
3 REPLIES
N/A

Re: How to center align a decimal value(including negative numbers) with 20 characters as total fixed length

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...

Re: How to center align a decimal value(including negative numbers) with 20 characters as total fixed length

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.

SELECT

CAST

(RPAD(LPAD(TRIM(COL1),(20 - CHAR_LENGTH(TRIM(COL1))) / 2 + CHAR_LENGTH(TRIM(COL1))), 20) AS CHAR(20)) COL1

FROM

(

SELECT

OREPLACE(

(

CAST( ( COL1 (FORMAT '-z(15)9.99')) AS VARCHAR(20))

),

' ','')

AS COL9

FROM TABLENAME

)

REPORT

;

Cheers

Krishna

N/A

Re: How to center align a decimal value(including negative numbers) with 20 characters as total fixed length

H Krishna,

no need for oTranslate, but your format defines leading blanks, simply change it:

CAST(( x (FORMAT '-(16)9.99')) AS VARCHAR(20))
or
TRIM( x (FORMAT '-(16)9.99'))