Need help handling null and empty values

Database

Need help handling null and empty values

Hello experts!!

I need help in handling null and empty values in 3 (data type) scenarios. Here is what I am doing currently and I want to know if I am missing something? Is this the right way to handle it?

1. varchar(10)
(CASE WHEN CHAR_LENGTH(TRIM(BOTH FROM COALESCE(MyField,''))) = 0 THEN 'Undefined'
ELSE TRIM(BOTH FROM MyField)
END) AS "MyNewField"
2. smallint
COALESCE(MyField,9999)
3. char(3)
(CASE WHEN CHAR_LENGTH(TRIM(BOTH FROM COALESCE(MyField,''))) = 0 THEN 'UND'
ELSE TRIM(BOTH FROM MyField)
END) AS "MyNewField"

Thank you for your help and time in advance!

Tags (2)
3 REPLIES

Re: Need help handling null and empty values

It looks like true.
N/A

Re: Need help handling null and empty values

Your handling for the character fields will work, but it is over-complicated.

You will be far more popular with:

1. CASE WHEN MyField = '' OR MyField is Null THEN 'Undefined' ELSE MyField End as MyNewField
2. Fine
3. CASE WHEN MyField = '' OR MyField is Null Then 'UND' ELSE MyField END

It is much more easy to follow when you are called out of bed at 2am!
WAQ
N/A

Re: Need help handling null and empty values

if there are some empty characters then it will be treated as 0 length string. Right?
eg
INSERT INTO table (MyField) VALUES (' ');

SELECT LENGTH(MyField) FROM table;

returns 0