Fixed Width issue

Question

Post reply

Fixed Width issue

kyle.doouss

SSCommitted

Points: 1513
More actions
December 2, 2011 at 6:32 am

#248940

I need to create the following for a fixed width file. I haveput the script I am using and the error I am getting. Any idea?
Some of the prices are more tha 2 decimal places so I haveput a round() in the script.
Glad of any help.
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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

TedT Right there with Babe Points: 796 More actions · Answer 1

Try somethign like this:

declare @Amount as varchar(10)

select @Amount = cast(cast(20.55 * 100 as int) as varchar(10))

SELECT STUFF(@Amount, 1, 0, REPLICATE('0', 12 - LEN(@Amount)))

_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain

TedT Right there with Babe Points: 796 More actions · Answer 2

And to handle the variable number of decimal places, you could try:

select POWER(10, cast(CHARINDEX('.',reverse(cast(@Amount as varchar(10))))-1 as int))

_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain

kyle.doouss SSCommitted Points: 1513 More actions · Answer 3

Thanks for the response. You have put 20.55 as hard test. I am having to do this on the vaules the query returns. Where whould you put the field name you are returning. I put it in the delcare but it does not like it.

Sorry I am very new to SQL

TedT Right there with Babe Points: 796 More actions · Answer 4

I'd probably create a function to do this, and use this to do the conversion.

Create function dbo.udf_StuffString

(@Value float)

Returns Varchar(12)

as

Begin

Declare @Multiple int,

@Output varchar(12)

Set@Multiple = casewhenCHARINDEX('.',reverse(cast(@Value as varchar(12)))) > 0

thenPOWER(10, cast(CHARINDEX('.',reverse(cast(@Value as varchar(10))))-1 as int))

else 1

end

Set@Output = cast(cast(@Value * @Multiple as int) as varchar(12))

set@Output = STUFF(@Output, 1, 0, REPLICATE('0', 12 - LEN(@Output)))

Return @Output

End

And then use it in the query like this:

Selectdbo.udf_StuffString(FieldName)

_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

kyle.doouss (12/2/2011)
I need to create the following for a fixed width file. I haveput the script I am using and the error I am getting. Any idea?
Some of the prices are more tha 2 decimal places so I haveput a round() in the script.
Glad of any help.
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.

You got this error because you are trying to cast 2699900.000000 as a varchar(9) which won't fit.

See if this will work:

;with Numbers(SomeVal)

as

(

select 20

union all

select 20.553

union all

select 2699900.000000

)

select right(replicate('0', 9) + replace(cast(CAST(round(SomeVal, 2) as numeric(9,2)) as varchar(25)), '.', ''), 9)

from Numbers

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kyle.doouss SSCommitted Points: 1513 More actions · Answer 6

Thanks for you help guys.

I came up with the following and it works. It was that little int hint! Cheers.

CAST(RIGHT('00000000'+ cast(cast(round(Price,2) *100 AS int)AS varCHAR(9)),9)AS CHAR(9))AS Price,