Solved

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

Posted on 2006-05-30
830 Views
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
Question by:BrianMack

LVL 13

Expert Comment

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 Comment

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 Comment

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

Author Comment

I found this solution from mglxxx.  It works great.

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

LVL 50

Expert Comment

can i suggest

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

?
0

LVL 50

Expert Comment

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

LVL 13

Expert Comment

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

Accepted Solution

PAQed with points refunded (125)

GranMod
Community Support Moderator
0

## Featured Post

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…