Null, Empty or Blank

[#1]

I have a field in a table that I need a WHERE statement on. The field is rejected order lines and will have code in the field if the line has been rejected. If the line has not been rejected it is null. However, if a Customer Service Rep(CSR) rejects the line (puts in a code) and then changes their mind and "blanks" the field, it is not NULL any more it is Empty or Blank. Sometime the CSR goes into the field and puts in a space to remove the rejected order code.

I need to see only items that have not been rejected. I currently have to using the following:

WHERE MyField like '' OR MyFiled like ' ' OR MyField like ' ' OR ISNULL(MyField);

I have also used :

WHERE len(MyField) < 2; but the code (currently only two characters) could eventually go to a three character code or four or . . . ..

Is there a better way to test the field to trap that there is "no code" in field.

Thanks,
Stephen

[erichshiino]

try:

where len(trim(MyField))>0

it will remove spaces then count characters

Rgds,

[#3]

That gives me everything "with a code" . I need everthing thing with out a code.

Stephen

[#4]

Using:

WHERE NOT len(trim(MyField)) >= 2

seems to work.

Thanks,

Stephen

[#5]

An old post, but if you still need an answer you can use PurgeChar(text, remove_char) (in script load) to remove unwanted characters, that includes spaces.

Carl