Export file with spaces at the end

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Export file with spaces at the end

Author

Topic

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-12-11 : 15:15:27

I can't seem to be able to figure out how to do this easily. What I need to do is select some records (with fields concatenated) that build a fixed with string and each row has to be exactly 80 characters in length. For those that are less than 80 I need to put in enough spaces to make it 80. I know I could use something like REPLICATE(' ',80-len(concatenated string)) as the last field in the concatenated fields but that seems a bit messy since the concatenated string is quite long. Is there a better way?

Here's what I have:

select rtrim(cardtype) +
rtrim(depositsinformation) +
rtrim(banknumber)+
rtrim(checking)+
rtrim(dbo.padstring(accountnumber,'0',16)) +
rtrim(dbo.padstring(Juliandate,'0',7)) +
rtrim(interestpaid) +
replace(rtrim(dbo.padstring(round(interestlow * 100 ,2),'0',11)),'.','') +
dbo.padstring('0','0',10) +
rtrim(keyblocksequencec) +
'0000000000000000' as text,
BankNumber, accountnumber, 1 as num
from table

I just need it to put spaces in at the end to make each row be 80 characters in length.

hey001us
Posting Yak Master

185 Posts

Posted - 2008-12-11 : 15:26:52

Did you tried in DTSX?

hey

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-12-11 : 15:34:31

Haven't tried that (I assume you mean SSIS or DTS). I was just trying to do it all in the SP. What I'm thinking about doing is wrapping the original select with another select and doing it that way. The original select would be a derived table and the concatenated fields are a field called 'text'. Then I can do "text+replicate(' ',80-len(text))".

hey001us
Posting Yak Master

185 Posts

Posted - 2008-12-11 : 15:42:50

Yes I mean SSIS. well you can do same way what you're doing if you need only SP using derived table.

hey

ddamico
Yak Posting Veteran

76 Posts

Posted - 2008-12-11 : 15:47:33

I did something like this. Using your idea of derived table

SELECT A.x + SPACE(80-LEN(A.x))
FROM (
select '0000000000' As x) A

hey001us
Posting Yak Master

185 Posts

Posted - 2008-12-11 : 15:54:58

is that okay? its seems best way on my point of view if you need to do in SP.

hey