December 28, 2011 at 8:14 am
Hi,
Can someone please help me how to replace and convert the below value to decimal
Example
0000567K AS 56.71
-0000904J as -90.42
and so on
I tried this and got the error
select member_ID,amountPAID,
replace(replace(replace( replace( replace ( replace
(INGREDIENT_COSTPAID,'A','1'), 'B','2'), 'K','2'),'C','3'),'{','0'),'J','1'),
CONVERT (decimal, 'INGREDIENT_COSTPAID') / 100
as amountPAID from dbo.stores
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
December 28, 2011 at 8:24 am
kutemom09 (12/28/2011)
Hi,Can someone please help me how to replace and convert the below value to decimal
Example
0000567K AS 56.71
-0000904J as -90.42
and so on
I tried this and got the error
select member_ID,amountPAID,
replace(replace(replace( replace( replace ( replace
(INGREDIENT_COSTPAID,'A','1'), 'B','2'), 'K','2'),'C','3'),'{','0'),'J','1'),
CONVERT (decimal, 'INGREDIENT_COSTPAID') / 100
as amountPAID from dbo.stores
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
That is because you are trying to convert the string literal 'INGREDIENT_COSTPAID' to a decimal which won't work.
Try this:
convert(decimal, replace(replace(replace( replace( replace ( replace
(INGREDIENT_COSTPAID,'A','1'), 'B','2'), 'K','2'),'C','3'),'{','0'),'J','1')) / 100
_______________________________________________________________
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/
December 28, 2011 at 8:38 am
I am a starter and Thank you for the help.It worked perfectly.And how can I get rid of those zeroes at the end
December 28, 2011 at 8:48 am
Something like this?
;with data(INGREDIENT_COSTPAID)
as
(
select '0000567K' union all
select '-0000904J'
)
select cast((replace(replace(replace(replace(replace(replace(INGREDIENT_COSTPAID,'A','1'), 'B','2'), 'K','2'),'C','3'),'{','0'),'J','1') * 1) / 100.0 as numeric(9,2))
from data
FYI, the output and sample data are slightly off. In your original post you said you wanted 56.71 and -91.42. Looks like either in your post of in your replace statements you got one of them reversed. 😉
_______________________________________________________________
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/
December 28, 2011 at 9:14 am
Hi Sean,
The first query worked perfect.
which gave me result
0000567K AS 56.710000
-0000904J as -90.420000
I need only 2 digits to the right of decimal
which would be
56.71
90.42
Thank a lot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply