Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gschwab
Starting Member
7 Posts |
 Posted - 2014-03-17 : 19:30:51
|
| I'm trying to create an output file in a specific layout. For some reason my output file is adding an extra 10 spaces between the Account Number and the Check Number in the statement below. The rest of the output file looks fine. Any idea where the extra 10 spaces are coming from? I need 1 Filler Space between these fields. Thanks for any help!SELECT DISTINCT CASE p.PaymentMethodID WHEN 10 THEN 'I' WHEN 60 THEN 'V' WHEN 50 THEN 'S' ELSE 'I' END+ CONVERT(CHAR(1), '')+ (REPLICATE('0', 20 - LEN(ba.AccountNumber))+ CONVERT(CHAR(20), ba.AccountNumber))+ CONVERT(CHAR(1), '') + (REPLICATE('0', 18 - LEN(p.CheckNumber)) + CONVERT(VARCHAR(18), p.CheckNumber))+ CONVERT(CHAR(1), '')+ (REPLICATE('0', 18 - LEN(replace(p.checkamount,'.',''))) + CONVERT(VARCHAR(18), case p.paymentmethodid WHEN 10 THEN REPLACE(p.checkamount,'.','')WHEN 60 THEN REPLACE(p.checkamount,'.','') WHEN 50 THEN REPLACE(p.checkamount,'.','') else 0 END))+ CONVERT(CHAR(1), '')+ REPLACE(CONVERT(VARCHAR(8), p.CheckDate, 112), '/', '') + CONVERT(CHAR(25), '')+ CONVERT(VARCHAR(50), y.PayeeName1)+ CONVERT(CHAR(56), '') |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-03-17 : 21:35:17
|
Use VARCHAR(20) instead of CHAR(20)....+ (REPLICATE('0', 20 - LEN(ba.AccountNumber))+ CONVERT(VARCHAR(20), ba.AccountNumber)) |
 |
|
|
gschwab
Starting Member
7 Posts |
Posted - 2014-03-18 : 16:53:54
|
| Thanks James! |
|
|
|
|
|
|
|
|
Extra spaces in my output