sexta-feira, 19 de setembro de 2014

selecting varchar: Is null or is empty?

When using TSql to check if a varchar is null or is empty we can remember:
- A nullable varchar is
1. not null when store a string or an empty string;
2. null only when not initialized, even with an empty string.

update fied1 = 'A string', field2 = '', field3 = null

We have:
Select * from table where field1 is null (0 row)
Select * from table where field2 is null (0 row)
Select * from table where field3 is null (1 row)
Select * from table where field1 = '' (0 row)
Select * from table where field2 = '' (1 row*)
Select * from table where field3 = '' (0 row)
Select * from table where (field2 = '' and field2 is null) (0 row) 
Select * from table where (field2 = '' and field2 is NOT null) (1 row**)

Well, the objective is to pay attention to our predicates of where clause, because sometimes some results are not expected when selecting empty strings

* this can be true for any amount of spaces
** pay special attention to this predicates working together

/*------------------------

Select * from select_varchar where field1 is null 
Select * from select_varchar where field2 is null 
Select * from select_varchar where field3 is null 
Select * from select_varchar where field1 = '' 
Select * from select_varchar where field2 = '' 
Select * from select_varchar where field3 = '' 
Select * from select_varchar where (field2 = '' and field2 is null) 
Select * from select_varchar where (field2 = '' and field2 is NOT null) 

------------------------*/
field1     field2     field3
---------- ---------- ----------

(0 row(s) affected)

field1     field2     field3
---------- ---------- ----------

(0 row(s) affected)

field1     field2     field3
---------- ---------- ----------
A String              NULL

(1 row(s) affected)

field1     field2     field3
---------- ---------- ----------

(0 row(s) affected)

field1     field2     field3
---------- ---------- ----------
A String              NULL

(1 row(s) affected)

field1     field2     field3
---------- ---------- ----------

(0 row(s) affected)

field1     field2     field3
---------- ---------- ----------

(0 row(s) affected)

field1     field2     field3
---------- ---------- ----------
A String              NULL

(1 row(s) affected)

Nenhum comentário:

Postar um comentário