Empty String in a column in sql server 2019- with Varying string length

One of the column of table contains Some NULL and Some Empty String. ''

Query I have written to filter those NULL and Empty string like.

SELECT COLA, COLB FROM TABLE WHERE COLB IS NOT NULL OR COLB !=''

Now the issue is empty string is looks like.

 ''
' '
'   '
'       '...... etc --- This is just example sample.

Is there any way to handle such scenario.
Please help.

>Solution :

Remove the IS NULL check, you don’t need it and it’s causing your results to be wrong.

When comparing a string, a (not) equal operator already implicity removes NULL values as NULL != <something> returns UNKNOWN, which isn’t TRUE. As you are checking the value isn’t NULL or not equal to '' you effectively get any rows that don’t have the value NULL as '' isn’t NULL.

There’s no need to also check if the string is only filled with whitespace, as trailing spaces are "trimmed" for comparison reasons. this means that '' and ' ' are considered equal, and so '' != ' ' would result in FALSE:

SELECT *
FROM (VALUES(''),
            ('    '),
            (NULL),
            (' '),
            ('a'))V(YourColumn)
WHERE YourColumn != '';