SQL DECIMAL column that supports and converts empty OR spaces into NULL

Question

I have a DECIMAL field in my SQL table, and I want to support EMPTY and SPACES as well. So, when the user enters nothing OR spaces only in that field, I still want the table to take it and convert that into NULL.

I tried using CASE in an INSERT statement, in SQL. I am using it under PHP. I tried this:

INSERT INTO test (id,value) VALUES ('@id',CASE WHEN LTRIM(RTRIM('@amount')) IS NULL THEN LTRIM(RTRIM('@amount')) ELSE NULL END);

This doesn't work. However, the exact same statement but for UPDATE works fine!

If I can solve the problem using a different approach, please advise?

Answer 1

Try this

NULLIF(LTRIM(RTRIM('@amount')),'')