May 14, 2008 at 2:43 pm
Hi All,
I have a very simple SSIS package that takes information from a SQL
server table and puts it into a table in an AS400 database. It is
working well except for one small issue. One of my fields is defined
as a decimal(10,4). When it transfers over to the AS400 table, it is
left justified - meaning that if the data in the field is '23.400' the
file in AS400 just has blanks after it. The AS400 programmer would
prefer it to have the blanks to the left so that essentially the
information is right justified. Is it possible to do this? I've been
wading through a lot of documentation but have not been able to find
anything on this topic.
Thanks in adavance for your help!
JCC
May 15, 2008 at 6:14 am
Search this site for padding numbers.
You should find some options such as:
RIGHT(SPACE(10) + CONVERT(VARCHAR,MyNum),10)
There are some more creative ways and probably some good functions if you do some searching.
If it is really a numeric field, it should not need padding, so your AS400 developer is probably storing numbers as text.
May 15, 2008 at 6:49 am
The SQL Server REPLICATE function also works well for this type of requirement:
The following example will take a value @strOldValue and pad it to 50 positions, right justified, padded with spaces. You will need to convert your original decimal value to a varchar first.
SET @strNewValue = REPLICATE(' ', 50 - Len(@strOldValue)) + @strOldValue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply