Fixed width export - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Fixed width export

Author

Topic

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-12-01 : 03:49:40

I am using the import and export tool to create a ficed width file for upload to the website. Glad of any help.

I need to create a view which will put 2.50 as 000000250 or 26.59 as 000002659. This is a char(9).

The idea is that a numeric field might have decimals, e.g. N(2) for 2 decimals or N for no decimals.
If the length of a numeric field is 8 it should be filled with eight zero’s and no spaces 00000000
If a value of the field is 5 and format is N the field should be filled with 00000005

If there are decimals N(2) the value 123.45 should be exported as 00012345
And so on.

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-12-01 : 04:24:03

I have done the following. If there is a better wya please say

cast(right('000000000'+CAST(round(Price,2)*100 as varCHAR(9)),9)as CHAR(9)),

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-01 : 04:24:50

declare @n numeric(12,2)
set @n=2.50
select right(replicate('0',8)+replace(@n ,'.',''),8)

Madhivanan

Failing to plan is Planning to fail

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-12-01 : 04:36:07

quote:
Originally posted by madhivanan

declare @n numeric(12,2)
set @n=2.50
select right(replicate('0',8)+replace(@n ,'.',''),8)

Madhivanan

Failing to plan is Planning to fail

Great thanks. Yes it is more tidy

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-12-02 : 06:24:35

I have reverted back to the below because if there was no . in the price eg 20 then it was not putting 00 to the right. The script mentioned above may be the best way to do it but it might need a bit of tweeking.

Pricing examples.
20 needs to = 000002000
20.55 needs to = 000002055

cast(right('000000000'+CAST(round(isnull(Price,''),2)*100 as varCHAR(9)),9)as CHAR(100)) as Price

error for the script below

Msg 232, Level 16, State 2, Line 1
Arithmetic overflow error for type varchar, value = 2699900.000000.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources