Search code examples
sql-servert-sqlcastingtype-conversionnvarchar

Changing the data type from NVARCHAR to DECIMAL is not working


I have a calculation, from which I get either ('/') or ('*'). This is saved in the variable "@op". The problem was, that I was not able to do some calculations with that, because of casting problems, so I casted the values together with my value from "@op". The result of that has the data type NVARCHAR.

The problem is, that I do not get the correct result?! Maybe the casting is not working...

And in general it was not possible for me to generate a decimal value from variable "@Factor", which has the data type NVARCHAR. Here you can see the cast command I tried:

SET @Factor = (SELECT(CAST(@Factor as Decimal(18,6))))

Because that was not working, maybe because of the dot, I tried to get the part before the dot and after it, to cast it in a different way, but unfortunately the wrong result comes out...

I am using SQL Server Managing Studio v17.7

DECLARE @Factor NVARCHAR(20)
DECLARE @op NVARCHAR(20)
DECLARE @num3 FLOAT

SET @num3 = 1.0
SET @op = (SELECT dbo.CalculateFactor1())

SET @Factor = 'SELECT ' + CAST(@num3 AS nvarchar(20)) + @op + CAST(RAND() AS nvarchar(20))

EXEC sp_executesql @Factor -- random factor

DECLARE @test0 NVARCHAR(20)
DECLARE @test1 NVARCHAR(20)
DECLARE @test2 NVARCHAR(20)
DECLARE @test3 NVARCHAR(20)
DECLARE @test4 NVARCHAR(20)

SET @test1 = '0.123456'

SET @test2 = LEFT(@test1, CHARINDEX('.', @test1) - 1) -- gives me the part in front of the comma
SELECT @test2

SET @test3 = SUBSTRING(@test1,(CHARINDEX('.',@test1)+1),7) -- gives me the part after the comma 
SELECT @test3

SET @test4 = LEFT(@test1, CHARINDEX('.', @test1) - 1) + '.' + SUBSTRING(@test1,(CHARINDEX('.',@test1)+1),6) -- gives me the complete number
SELECT @test4 

SET @test0 = (SELECT(CAST(LEFT(@test1, CHARINDEX('.', @test1) - 1) + '.'  + SUBSTRING(@test1,(CHARINDEX('.',@test1)+1),6) AS DECIMAL(18,6)))) 
SELECT @test0

DECLARE @solution DECIMAL
SET @solution = @test0 * 200.0
SELECT @solution 

-- result is 20
-- expected result 24.6912 

Solution

  • Need to be explicit

    DECLARE @solution DECIMAL(18,6)
    SET @solution = cast(@test0 as decimal(18,6)) * 200.0
    SELECT @solution