Testing spaces in SQL Server

Question

I have several empty values ​​in my table, and I cannot catch them in the IF statement.

I tried

IF @value = '' and if @value = NULL , and none of them catch empty values. Is there any way to check if varchar is completely space?

Aha! Turns out I tested the error. Thank you

Answer 1

To compare with NULL, use the IS NULL keyword.

 --Generic example: SELECT * FROM MY_TABLE WHERE SOME_FIELD IS NULL; --Instead of SELECT * FROM MY_TABLE WHERE SOME_FIELD = NULL; 

Answer 2

 ltrim(rtrim(isNull(@value,''))) = '' 

Answer 3

if length (@value) = 0 or @value is null

Answer 4

(LTRIM (RTRIM (@Value)) = '' should do the trick.

Answer 5

where length (rtrim (ltrim (yourcolumnname))) = 0 OR column_name is null

Answer 6

Instead of doing over-string manipulation with LTRIM AND RTRIM , just search for the expression for the first "non-space".

 SELECT * FROM [Table] WHERE COALESCE(PATINDEX('%[^ ]%', [Value]), 0) > 0 

Answer 7

I just did some testing and found out something interesting. I wrote my queries like this:

 SELECT * FROM TableA WHERE Val IS NOT NULL AND LEN(RTRIM(LTRIM(Val))) > 0 

But you really don't need to check for null, all you have to do is check the length after trimming the value.

 SELECT * FROM TableA WHERE LEN(RTRIM(LTRIM(Val))) > 0 

This option preempts zeros, as well as any columns with a space.

As it turns out, you don't need to trim the value because SQL Server ignores trailing spaces, so all you really need is: SELECT * FROM TableA WHERE LEN (Val)> 0

Answer 8

You may have fields with multiple spaces (''), so you will get better results if you trim this:

 where ltrim(yourcolumnname) = ''