Monday, March 26, 2012

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
>
>

No comments:

Post a Comment