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 whenisnumeric(@test) = 1 THEN CONVERT(DECIMAL(38,2), @test)
ELSE @test
end

Explanation / Answer

Find the modified query below.

Here , i am checking the value is numeric or not.

ANSWER:

declare @test varchar(MAX)
set @test = ''
select case whenisnumeric(@test) < >1 or @test LIKE '%[^-.0-9]%'

THEN CONVERT(DECIMAL(38,2), @test)

ELSE @test
end

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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