Re: Problem: Like-Statement and NULL-Fields

"Volker Schmid" <Info_NoSpam_at_Inspirant.de> writes:

> Hi again,
>
> I found some NG-archived threads to this and don't want a discussion if NULL
> is ok for an empty string or not. I just need a workaround to get the needed
> behaviour. If I don't set the value of a Flags-Field I wan't it to act like
> ''. I need to find those empty ('') field's with the NOT LIKE-Statement.
>
> Can someone help?
>

Well, I think you really only have two choices, one of which you indicate is not possible -

1. (the one you can't do?) Add an OR flags_col IS NULL to your SQL
2. You said you tried modifying your table definition, but it would not work. I don't think you can modify a column and change it to NOT NULL if it already has rows which are NULL. You would need to set the NULL columns to some value first. This may also be true if you want to give the column a default value, but I'm not sure about that. Try setting all your nulls to some default non-NULL value with an sql update. then try changing the table definiiton.

If you still cannot modify the table, then maybe the good old brute force approach will work. Copy the contents to a temp table. drop the original table. recreate the table with the modified definition and then re-load the table with the data from the temp table. Of course, you will need to be careful to check what constraints are on the table - you could get badly burnt by things like foreign keys with cascade on deletes etc.

I would really make certain you cannot change the SQL - that would be the easiest and probably safest approach - I'd be very careful about adding a NOT NULL constraint on the column as you don't know what other scripts etc this may affect/break.

Tim Received on Tue Oct 22 2002 - 02:09:42 CDT