Convert to decimal but precision is random

[jluost1]

In my database, I have a column that holds numeric value as string. When I say numeric, it is positive and it could be any one of the following format: '999','999.9','999.99999'. (The precision is uncertain.)

In my sql query, I would like to get the string value and compare it with another numeric value.

I tried to convert both of them to be decimal, but I don't know the precision (If I leave precision blank, the default 0 is taken). If I convert 444444.55 to float I got 444444.54999999999. If I convert 444444.55 to real I got 444444.56. None of them is what I want.

So, How can I convert '999','999.9','999.99999' to be exactly 999,999.9,999.99999 respectively?

 

[aalmeida]

What you have to do is count how many characters you have after the '.' and then us the result to make up the correct precision, just as an example
DECLARE @Start int
DECLARE @Precision int
DECLARE @Scale int
Declare @Char varchar(10)
DECLARE @SQL varchar (60)
set @Char = '99999.9999'
SELECT @Start = CHARINDEX ( '.' , @Char )
SET @Scale = LEN (@Char)- @Start
SET @Precision = LEN (@Char)
SET @Sql = "SELECT cast ("+@Char+" as decimal("+cast(@Precision as varchar(2))+", "+cast(@Scale as varchar(2))+&quot) as 'Results'"
EXECUTE (@Sql) AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"