How to see non null values?

SQL Server Administration
1

I have a table which does not allow nulls in ColumnA. When I select * where ColumnA is not null, millions of rows are returned, most of them have no visible value
.
They may contain 1 or more spaces but how can I know for sure?
Thanks,

2

Compare the column to whatever values you want to check for.


SELECT ColumnA,
    CASE WHEN ColumnA = SPACE(1) THEN 'Space(s)'
        WHEN ColumnA LIKE '%[' + CHAR(13) + CHAR(10) + ']%' THEN 'CR/LF'
        --WHEN ...
        ELSE 'Other' END
FROM dbo.table_name
WHERE ColumnA IS NOT NULL AND 
    ColumnA NOT LIKE '%[A-Z][0-9]%' /*exclude "normal" values*/
2 Likes
3

If you want to find only those items that have a value this is NOT NULL, not an "empty string" and NOT BLANK, then your query would look like the following...

 SELECT *
   FROM dbo.YourTable
  WHERE ColumnA > ' '
;

... and, no, it doesn't matter how many spaces there may be on any given row for ColumnA. The code above will skip them all.

1 Like
4

gt will not always work (for example, the column value starts with a CR or LF). You should use <> instead.

1 Like
5

True 'dat.

Here's some code that demonstrates what Scott is talking about (there are a lot of others but these suffice for demo.

   DROP TABLE IF EXISTS #MyHead
;
 CREATE TABLE #MyHead
        (
         SomeString VARCHAR(10)
        ,Comment    VARCHAR(20)
        )
;
 INSERT INTO #MyHead
        (SomeString,Comment)
 VALUES  (NULL,'NULL')
        ,('', 'Empty String')
        ,(SPACE(1), '1 Space')
        ,(SPACE(9), '9 spaces')
        ,(CHAR(13)+CHAR(10),'CrLf')
        ,(CHAR(9),'Tab')
        ,('X','Non-Null, Non-Blank')
        ,(CHAR(160),'Hard Space')
;
--===== Using ">" misses Control Characters
 SELECT *
   FROM #MyHead 
  WHERE SomeString > ''
;
--===== Using "<>" picks up on Control Characters
 SELECT *
   FROM #MyHead 
  WHERE SomeString <> ''
;

Here's the output from above...

image

If you want to treat "Control" characters (ASCII value <= 31) as if they were "blanks" for search purposes (which is what most people want to do), then use ">".

If "Control" characters on an otherwise blank or empty string need to be included, the use "<>" like Scott said..

For some "unicode" variants, neither may work. I don't have an example for those, though.

6

If it does not allow nulls why are there so many blanks? Sounds like users find a hole in the system by adding a space to bypass some prompt in app layer that tells them this needs to be filled out. Should they be set (maybe even implementing a default non null value) to something more meaningful?

And what are you trying to solve while doing this query on rows with such values. Is the effort to find the count?

2 Likes
7

Now there's an excellent set questions!

8

Blanks (empty string) <> NULL

And yes - I have found that users will always find a way around filling out a text box they don't want to fill out. I have seen spaces, periods, dashes, carets, tilde, pound, etc...

The app developers need to consider those scenarios and validate data entry is correct, but often decide it is just easier to the let the database handle it. And no one puts any default constraints to check for invalid strings - and even if you do create a constraint it won't handle all possibilities and someone will figure out a way around it.

2 Likes
9

Such as, if you force letters and not just non-letters, they will enter 'abcd' or the like.

I think you're better off with NULLs than with garbage.

2 Likes
10

Blanks (empty string) <> NULL

No kidding @jeffw8713 :smile::smile::smile:

Yeah the constraint I agree would be as bad if not worse. Cant account for all permutations

11

Sorry - just had to put that in there... :slight_smile:

1 Like
12

That's the only truth I've heard today! And, it's an IMPORTANT truth!.