How to check for field-content in oracle

[Anon]

Hi, what is the easyest way, to check a varchar2 filed, wheter there is anything in it.

Now I use ...trim(trgt_cltr_cd) <> ''...
But I know wheter this is a good idea and I ask myself, wheter there is somethink like isset in php.

Thx, EOF Thomas

[[deleted]]

Normal practise is to set empty fields to NULL, and check for NULL fields:

SELECT * FROM table WHERE field IS NOT NULL;

BTW, TRIM is no good on varchar(), they are already 'trimmed'. Only CHAR fields have extra spaces.

[Anon]

Ok, thx so far, but how can I default a field to NULL, when it has a NOT NULL constraint?
Or did I get something wrong here.

Thx, for the varchar2 hint!

CU

[[deleted]]

Almost.

The 'normal' way of doing it is to design the database so that there is no 'NOT NULL' constraint.

Then if you really don't want to enter a value for that field, it will contain NULL.

If a field is defined as 'NOT NULL' then obviously you can't check for IS NULL, and you will have to check for <> ""

SELECT *
FROM table
WHERE field <> "";

Not pretty, but it works.

Note: unless I am much mistaken, Oracle treats an empty string as NULL, and it will refuse to let you do:

UPDATE table
SET field=""
WHERE foo=bar;

if 'field' is defined as NOT NULL.

[Anon]

Hi again, I've changed the application now, so that the relevant field is a field, where null is allowed. And now my clause is ...is not null... which woks fine.

Before I did this, I've played around with your suggestions and I want to stat two comments on them:

1.) I can't use double quotes in clauses, so
-->SELECT * FROM table WHERE field <> ""; <--
doesn't get through the parser. Does it work with your
Oracle?

2.) And this is what I just can't understand, when I use single quotes like
-->SELECT * FROM table WHERE field <> ''; <--
I MUST have at least one space between the single quotes, otherwise I get absolutely no match. This is strange to me, as varchar2 fields are trimed (as you said).

CU