asked on

adding leading zeroes in SSIS

Hi All,

I have a column names as listed below, Data types are Varchar

     cdn#        CC#        CCType

      B5793846      ****-****-****-9043 Visa

For the above i Need to add zeros and display in the below format,

cdn#        CC#        CCType

000000000000B5793846      0000000000009043 CC(Constant)

Currently I am working on the below Query, Please can any one help me i tried with Format function. Please kindly review My Query and let me know any modification to be done.

select right('D00000000000' + REPLACE(CONVERT(VARCHAR,amount),'.',''),11) AS AMOUNT,
  [Account #]as cdn#,
  [CC ACCT]as CC#,
  [CardType] as CCType 
  from [PayPal_staging].[dbo].[VendorFiles] as a
   join [CUBS].[dbo].[Client] as d
  on d.PK_Client=CONVERT(varchar(250),a.client)
  where  PK_Client in  ('SWMC203','SWMC600','SQMC203','SQMC600','SBMC203','SBMC600',
    'PROV203','PROV600','SWRC203','SWRC600','SMCC203','SMCC600',
    'SWIS203','SWIS600')
Union all
  Select [Amount],[CDN #] as cdn#,[Card_No] as CC#,[Card_Type] as CCType 
  from [PayPal_staging].[dbo].[VirtualFiles] as b
   join [CUBS].[dbo].[Client] as d
  on d.PK_Client=CONVERT(varchar(250),b.[Client_Id])
  where  PK_Client in  ('SWMC203','SWMC600','SQMC203','SQMC600','SBMC203','SBMC600',
    'PROV203','PROV600','SWRC203','SWRC600','SMCC203','SMCC600',
    'SWIS203','SWIS600')
  union all
  select [SV10_Amt] as Amount,[Cdr_Id] as cdn#,[SV07_Last4] as CC#,[CardType] as CCType
  from [PayPal_staging].[dbo].[IVRFiles] as c
   join [CUBS].[dbo].[Client] as d
  on d.PK_Client=CONVERT(varchar(250),c.[SV03_Clientnbr])
  where  PK_Client in  ('SWMC203','SWMC600','SQMC203','SQMC600','SBMC203','SBMC600',
    'PROV203','PROV600','SWRC203','SWRC600','SMCC203','SMCC600',
    'SWIS203','SWIS600')

Open in new window

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chandan m

ASKER

Hi
I ran the above Query only for few table is getting changed and for rest its not getting changed, Below is the Query which i run its not getting changed. Please can you review my Query and let me know what is the mistake.

select right('00000000000' + REPLACE(CONVERT(varchar,[SV10_Amt]),'.',''),11)as Amount,
  right('00000000000000000000' + [Cdr_Id],20) as cdn#,
   right('0000000000000000' +REPLACE( REPLACE(CONVERT(VARCHAR(20),[SV07_Last4]),'*',''),'-',''),16)as CC#,
  [CardType] as CCType
  from [PayPal_staging].[dbo].[IVRFiles] as c
   join [CUBS].[dbo].[Client] as d
  on d.PK_Client=CONVERT(varchar(250),c.[SV03_Clientnbr])
  where  PK_Client in  ('SWMC203','SWMC600','SQMC203','SQMC600','SBMC203','SBMC600',
    'PROV203','PROV600','SWRC203','SWRC600','SMCC203','SMCC600',
    'SWIS203','SWIS600')

Open in new window

Below is output I get when i run the above Query.....

Amount       cdn#
0      6502693
     6507155
     6509524
     5970527
     6042481
4      6080827
0      6214004
0      6256971
0      6326587
0      6317412
     6420138

The data type is Varchar in the amount column and CDN# its not getting changed. Please anybody help me in reviewing the Query.

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

suggested solutions are valid and verified...