understanding null vs blank or empty string values

NathanDorsch
NathanDorsch Member
in Charting

I've always struggled with figuring out how to filter by fields with nulls vs blanks... for example, if I have a formula to identify if a field is null (this will not work with fields that contain empty strings or blank values, correct...?). I've tried using charlength functions to say (when charlength' is greater than 0 then not blank, etc....) but this doesn't seem to work with null values....

Is there a simple formula/way to identify if a field is blank (regardless of null or empty strings)?

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓ 
    CASE WHEN COALESCE(TRIM(`field`), '') = '' THEN 'Blank' ELSE 'Not Blank' END

    COALESCE will return the first non-null value it finds, used in this case it's saying if the field is null return an empty string. I've wrapped the field in a trim to remove any leading or trailing whitespaces in case someone has a value of all spaces to ignore. Then it is compared to an empty string after it's been converted.


    A more verbose way would be:

    CASE WHEN `field` IS NULL or TRIM(`field`) = '' THEN 'Blank' ELSE 'Not Blank' END


    NULL is a very specific value. The only way to check for null is to use IS NULL or a COALESCE / IFNULL function to default it to a specific value and compare that value.

    Most functions you use on NULL or comparison will result in a NULL value back because NULL represents the absence of data.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓ 
    CASE WHEN COALESCE(TRIM(`field`), '') = '' THEN 'Blank' ELSE 'Not Blank' END

    COALESCE will return the first non-null value it finds, used in this case it's saying if the field is null return an empty string. I've wrapped the field in a trim to remove any leading or trailing whitespaces in case someone has a value of all spaces to ignore. Then it is compared to an empty string after it's been converted.


    A more verbose way would be:

    CASE WHEN `field` IS NULL or TRIM(`field`) = '' THEN 'Blank' ELSE 'Not Blank' END


    NULL is a very specific value. The only way to check for null is to use IS NULL or a COALESCE / IFNULL function to default it to a specific value and compare that value.

    Most functions you use on NULL or comparison will result in a NULL value back because NULL represents the absence of data.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories