All Forums Tools
Cheryl 3 posts Joined 06/07
22 Aug 2008
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.

j355ga 100 posts Joined 12/05
25 Aug 2008

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.

Jeff

karver 17 posts Joined 01/09
29 Jan 2009

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"

vhari 111 posts Joined 12/08
29 Jan 2009

if i is the column,'"'||cast(i as int format 'zzzzz') || '"'

karver 17 posts Joined 01/09
30 Jan 2009

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.

dnoeth 4628 posts Joined 11/04
30 Jan 2009

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 alignedSELECT 22 AS col, col (FORMAT 'ZZZZZ') (CHAR(5)), CAST((col (FORMAT 'ZZZZZ')) AS CHAR(5))Dieter

Dieter

You must sign in to leave a comment.