SQL Right Justify column

Tools
Fan

SQL Right Justify column

I am trying to format a decimal field to get the commas displayed using: CAST((( SAUP) (FORMAT 'ZZ,ZZZ,ZZ9.99')) AS CHAR(13))) (NAMED VALUE1), which works but now I want to right justify the data as if it were an integer field. I cannot find any functions or command to right justify this character data. Please help. Thank you.
5 REPLIES
Teradata Employee

Re: SQL Right Justify column

The CHAR cast is not needed. Just use the format:

SAUP (FORMAT 'ZZ,ZZZ,ZZ9.99' (NAMED VALUE1),

If you are exporting using FastExport this will not work unless you specifiy format TEXT in the export command.
Enthusiast

Re: SQL Right Justify column

I have an integer value that I want to right justify. anybody know how to do that? using lpad means i have to cast the integer value to char, and doing that removes some of the numbers (leaves only the first character)

say i have a value: '22' and the max value for that field is 5 integers. I would need it to come out as:

blankspaceblankspaceblankspace22 -> total of 5 characters ( 22)

I would need that since I will be appending quotes before and after the value such that the end result will be: " 22"

Teradata Employee

Re: SQL Right Justify column


if i is the column,

'"'||cast(i as int format 'zzzzz') || '"'
Enthusiast

Re: SQL Right Justify column

thanks for the response vhari :)

I was also able to get my required output (this syntax handles removing the garbage characters when using fastexport):
lpad ((trim(A.field (FORMAT 'ZZZZZ'))),5,' ') (CHAR(5))

*where A.field is in integer format.

Senior Apprentice

Re: SQL Right Justify column

In Teradata there are two different ways to do a typecast:
- new ANSI style using CAST(col AS newdatatype) -> left aligned
- old Teradata style using col (newdatatype) -> right aligned

SELECT 22 AS col,
col (FORMAT 'ZZZZZ') (CHAR(5)),
CAST((col (FORMAT 'ZZZZZ')) AS CHAR(5))

Dieter