Right-justify a column on export to text

Hello, I have a column (AccountNo per the below create script) that displays
properly with leading zeroes to fill a 22-character column while in SQL.
However, when I use a DTS export to a standard text file with no
transformation, it left-justifies. Could someone pls advise how I can get i
t
to export in a fixed length file preserving the leading zeroes and
right-justified? Thanks, Pancho.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GVMOI2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GVMOI2]
GO
CREATE TABLE [dbo].[GVMOI2] (
[TranDateSold] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerID] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TaxIDNum] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TaxIDType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicationCode] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountNo] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TraceNbr] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreditAmtCash] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DebitAmtCash] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreditAmtChecks] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DebitAmtChecks] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TellerID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BranchNo] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CheckReferenceNbr] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CheckNbr] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BankNumber] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Remitter1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Payee1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ThirdParty] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Denomination] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IDNumber] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IDIssueBy] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IDOthers] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GOGenerally, the following expression should create the string with the value
right-justified: SELECT RIGHT( SPACE(22) + CAST( col AS VARCHAR ) , 22 )
Anith|||Well, it is right-justifying but losing my leading zeroes. I wish for the
column to generate like this:
0000000000000915999999
0000000000009160000000
Our data has varying length. In the example above, our source data begins
with the 9 and we would like to have leading zeroes as needed to make the
column 22 characters wide. We want to export it to a text file and keep the
zeroes but also keep the right-justified format.
"Anith Sen" wrote:

> Generally, the following expression should create the string with the valu
e
> right-justified: SELECT RIGHT( SPACE(22) + CAST( col AS VARCHAR ) , 22 )
> --
> Anith
>
>|||Instead of SPACE(22), use REPLICATE( '0', 22 ).
Anith|||"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:0213C7CC-C11D-40BF-8314-2FE240B37C77@.microsoft.com...
> Well, it is right-justifying but losing my leading zeroes. I wish for the
> column to generate like this:
> 0000000000000915999999
> 0000000000009160000000
> Our data has varying length. In the example above, our source data begins
> with the 9 and we would like to have leading zeroes as needed to make the
> column 22 characters wide. We want to export it to a text file and keep
> the
> zeroes but also keep the right-justified format.
> "Anith Sen" wrote:
>
I use this to right justify and zero fill a string in one of my
applications. May not be the best solution but it is what I came up with
when faced with a similar problem.
SELECT REPLICATE('0', 22 - LEN(ISNULL(column, REPLICATE('0', 22)))) +
ISNULL(column, REPLICATE('0', 22))
Kevin|||Well, both approaches work to create the column and display leading zeroes,
right-justified. However, exporting to a flat file, the zeroes remain but i
t
is defaulting to left-justified. Is there something I need to set in DTS?
Using Kevin's script I got the column to create with leading zeroes, 22
displaying and right justified, but it created a varchar column width of
8000. I would like it to be 22 characters wide in the output file. In DTS
I
changed size to 22 and tried type varchar and char but both resulted in
left-justified output columns in the text file.
"Kevin Haugen" wrote:

> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:0213C7CC-C11D-40BF-8314-2FE240B37C77@.microsoft.com...
> I use this to right justify and zero fill a string in one of my
> applications. May not be the best solution but it is what I came up with
> when faced with a similar problem.
> SELECT REPLICATE('0', 22 - LEN(ISNULL(column, REPLICATE('0', 22)))) +
> ISNULL(column, REPLICATE('0', 22))
> Kevin
>
>

right justify left zero fill

I have to build a text file with a dollar amount that must be right
justified, left zero filled and no decimal. The field I am extracting data
from is 'amount' and defined as decimal 9(15,2).
Stan Gosselin
On Fri, 28 Oct 2005 05:35:03 -0700, Stan wrote:

>I have to build a text file with a dollar amount that must be right
>justified, left zero filled and no decimal. The field I am extracting data
>from is 'amount' and defined as decimal 9(15,2).
Hi Stan,
You can use the following expression:
RIGHT(REPLICATE('0', 11) + LTRIM(CAST(YourColumn * 100 AS INT)), 11)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks again Hugo. I'm just getting exposed to SQL and, because of a short
deadline, I am being lazy about finding the answers. I shoudl be looking
stuff up the old fashioned way.
Stan Gosselin
"Hugo Kornelis" wrote:

> On Fri, 28 Oct 2005 05:35:03 -0700, Stan wrote:
>
> Hi Stan,
> You can use the following expression:
> RIGHT(REPLICATE('0', 11) + LTRIM(CAST(YourColumn * 100 AS INT)), 11)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||Hugo,
If you don't mind, I have a variation on the last question. I have the same
type field (decimal 9(15,2)) and the recipient of a conversion .txt file want
to see just the meaningful digits, no decimals, no zero fill but does want to
see a trailing minus sign for negative numbers. So they want to see 550.45
as 55045 and -45.25 as 4525-.
Should I be sending things like this or should I do a re-post?
Thanks for understanding.
Stan Gosselin
"Stan" wrote:
[vbcol=seagreen]
> Thanks again Hugo. I'm just getting exposed to SQL and, because of a short
> deadline, I am being lazy about finding the answers. I shoudl be looking
> stuff up the old fashioned way.
> --
> Stan Gosselin
>
> "Hugo Kornelis" wrote:
|||On Wed, 2 Nov 2005 13:24:02 -0800, Stan wrote:

>Hugo,
>If you don't mind, I have a variation on the last question. I have the same
>type field (decimal 9(15,2)) and the recipient of a conversion .txt file want
>to see just the meaningful digits, no decimals, no zero fill but does want to
>see a trailing minus sign for negative numbers. So they want to see 550.45
>as 55045 and -45.25 as 4525-.
>Should I be sending things like this or should I do a re-post?
>Thanks for understanding.
Hi Stan,
Sending like this is fine, though a repost might attract more eyes.
Generally, if the subject really changes, post a new message. If it's a
variation on the question, use a reply.
I see that Steve has already answered the question. I trust that his
reply is what you wanted.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks for the direction Hugo!
Stan Gosselin
"Hugo Kornelis" wrote:

> On Wed, 2 Nov 2005 13:24:02 -0800, Stan wrote:
>
> Hi Stan,
> Sending like this is fine, though a repost might attract more eyes.
> Generally, if the subject really changes, post a new message. If it's a
> variation on the question, use a reply.
> I see that Steve has already answered the question. I trust that his
> reply is what you wanted.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
sql