how to check a MEMO (text) field is empty? 1

[LucieLastic]

hi All

I have a type 'text' column in my field, how can I check this is empty? The following doesn't work, do I need to use convert ?:

where textfield is null

where textfield = ''

lou

 

[sybaseguru]

for text fields you can only use like comparison. See below:

1> create table abc(col1 int not null, text_field text null)
2> go
1> insert abc values(1,null)
2> go
(1 row affected)
1>  insert abc values(1,'some text here')
2> go
(1 row affected)
1> select * from abc where text_field is null
2> go
Msg 306, Level 16, State 1:
Server 'SYB_CRIDR1', Line 1:
TEXT and IMAGE datatypes may not be used in a WHERE clause, except with the LIKE expression.
1> select * from abc where text_field not like ''
2> go
 col1       
         text_field                                                   -----------
         -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
           1
         some text here                                                        (1 row affected)

To solve the probblem use convert function as below

1> select * from abc where convert(varchar(255),text_field) is null
2> go
 col1       
         text_field                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
 -----------
         -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
           1
         NULL                                                        
(1 row affected)

Hope this helps

 

[LucieLastic]

hi Sybaseguru

Thanks for that, really useful. I managed to get it working like this too:-

where not convert(varchar(1), textField) is null

Is it okay to have varchar length = 1?

lou

 

[sybaseguru]

Yes should be OK. But do not forget that "not" is slower than others