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

Question

Hi everyone,

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!

Answer 1

This works fine for me:

SELECT CONVERT (decimal, '00000001987600130') / 100000

The reason why the first one didnt work is because the result of the CAST is an integer, and dividing an integer by 100000 rounds / truncates it (not sure which) so that it is still an integer.

Answer 2

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.