Search code examples
sql-server-2005castingdecimalvarchar

Converting this varchar to a decimal with the appropriate decimal point?

I've been playing with cast()s and such with this and can't seem to get things to work. I have a varchar string that's 18 characters long that I'd like to convert or cast to a decimal, with five decimal places. So for instance, this string:

00000001987600130

Would become 19876.00130

It's the case that I'll always have a 17 character string, with the last five characters reserved as the decimal place.

I've been playing with casts and converts but I'm not quite there. For instance, these statements get me (sort of) close but not exactly.

select CAST('00000001987600130' as bigint)/100000.0

select (convert(decimal(17,5),left('00000001987600130',12),0))

If you have a suggestion I'm happy to try it. Thanks!

Solution

  • To ensure you get what you want do a final CAST to ensure decimal(17,5) exactly

    SELECT CAST((CAST('00000001987600130' AS decimal) / 100000) AS decimal(17,5))

    Otherwise, the output type is not correct in scale or precision and may have effects later.