[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1100

# How do I convert a numeric to char, right justified

Hi,

I'm trying to create a fixed column width file to be used by a legacy application.  one particular column (W1RFAMT) is  a numeic amount that I need to translate into character right justified.  An example is \$1,234,56 would be '      123456'.  How can I convert the field?

SELECT  CHAR(W1CORP,4)||
CHAR(W1BRCH,4)||
CHAR(W1ACCT,12)||
DIGITS(DECIMAL(W1TRNCD,4,0))||
CHAR(W1SYS,4)||
CHAR(W1PRIN,4)||
CHAR(W1NAME,32)||
CHAR(W1CITY,18)||
CHAR(W1STAT,2)||
CHAR('0000',16)||
CHAR(W1GRP#,3)||
DIGITS(DECIMAL(W1ZIP,9,0))||
CHAR(W1RFAMT),12)||    <---- needs to be char right justified
CHAR(W1DLER,16)
FROM    RCVOPSTEST.CBR901
0
BrianMack
• 3
• 2
• 2
• +1
1 Solution

Commented:
um, your column W1RFAMT, what is its native datatype?
if its integer, it can t have \$ and decimal places
if its decimal, it cant have commas
if its decimal, then is it defined with decimal places?
0

Author Commented:
W1RFAMT will always be positive by definition.  It is numeric 9,0 and does not contain any '\$' or ',' or '.'. The problem is when I used DIGITS(DECIMAL()) it retruned '000000123456'.  I need it to look like '      123456' (padded with spaces).
0

Author Commented:
If I just do CHAR(W1RFAMT) it returns '123456      '.
0

Author Commented:
I found this solution from mglxxx.  It works great.

REPEAT(' ', 12 - LENGTH(TRIM(CHAR(W1RFAMT)))) || TRIM(CHAR(W1RFAMT))||
0

Commented:
can i suggest

Right(space(12)+trim(char(w1rgamt)),12)

?
0

Commented:
sorry
Right(space(12) concat trim(char(w1rgamt)),12)
0

Commented:
if your happy with your own solution post a message in community support to have your points refunded but leave the question in the database for future reference for other users. Thanks
0

Commented:
PAQed with points refunded (125)

GranMod
Community Support Moderator
0

## Featured Post

• 3
• 2
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.