Hi, everyone. I need a help in stored procedure SQL. I have Column named FloatNu

Question

Hi, everyone.

I need a help in stored procedure SQL. I have Column named FloatNum which data type is VARCHAR(MAX), I want when the column has any number converted to two decimal places it and place it in the column, and if column has a NULL value return NULL and if column has an EMPTY value return EMPTY or have any letter value return a same value in the column, like if column has a word 'HI' return HI, my only problem with this Query is where if column has an EMPTY Value or has any (WORD / Alphabet)Value in Column @test I am getting this error. ( Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.) this is my Query, is anybody can help me, please?

best Regards.

declare @test varchar(MAX)
set @test = ''
select case when Isnumeric(@test) = 1 THEN CONVERT(DECIMAL(38,2), @test)
ELSE @test
end

Explanation / Answer

declare @test varchar(5)
set @test = ''

Select case when @test is null then null
           when @test='' then ''
           when ISNUMERIC(@test) = 1 THEN CAST(CONVERT(decimal(18,2),cast(@test as float)) as varchar(10))
else @test
   End

Please use the above query, it will resolve your issue.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.