Unloading Null Values, 0 length strings and spaces...
The poster (Informix 7.31 on Solaris 2.6) wanted UNLOAD to mark NULLs distinctly, since NULLs, empty strings and blanks all seemed to come out as "||". Replies showed the distinction already exists: for CHAR columns a NULL unloads as "||" while empty/blank values unload as "| |" (padded to at least one blank), and VARCHAR columns show empty strings escaped and blanks preserved, so the difference is visible unless post-processing strips it. One suggestion to temporarily replace NULLs with a marker string was criticised as unsafe if real data contains that string.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
All, I am wondering if anyone knows a way to force the unload statement into unloading the word null or something like that so that I can differentiate a null value from a space or a 0 length string. With the unload in its current state all the above values look like || when unloaded... This is a maint. nightmare for me since we have some tables with bad data. I'm on Informix 7.31 and sun 2.6. Thanks much, Michael Brizendine michaelb@pegsinc.com
Hi, I mean, the easiest way is to substitute nulls with 'any_string' in the table, unload it and then set it back to nulls Peter (may be it's more complex as I think) Michael Brizendine wrote: > All, > > I am wondering if anyone knows a way to force the unload statement into > unloading the word null or something like that so that I can > differentiate a null value from a space or a 0 length string. With the > unload in its current state all the above values look like || when > unloaded... This is a maint. nightmare for me since we have some tables > with bad data. I'm on Informix 7.31 and sun 2.6. Thanks much, > > Michael Brizendine > michaelb@pegsinc.com
I have 7.30 running on Sun 2.6 working with the following behaviour
when this script is run
create table junk (
junk1 char(5),
junk2 char(5)
);
insert into junk values ('null',null);
insert into junk values ('zero','');
insert into junk values ('one',' ');
insert into junk values ('more',' ');
unload to junk.unl
select * from junk;
junk.unl shows the following lines.
null|| (no blank between pipes)
zero| | (one blank between pipes)
one| | (one blank between pipes)
more| | (one blank between pipes)
When the column junk2 is defined as VARCHAR, this is what happens
null||
zero|\\ |
one| |
more| |
Either way, there is a clear difference between null and blanks. Isn't
Informix great!
Rudy
In article <7m2dk6$4lr$1@news.xmission.com>,
Michael Brizendine <michaelb@pegsinc.com> wrote:
>
> All,
>
> I am wondering if anyone knows a way to force the unload statement
into
> unloading the word null or something like that so that I can
> differentiate a null value from a space or a 0 length string. With
the
> unload in its current state all the above values look like || when
> unloaded... This is a maint. nightmare for me since we have some
tables
> with bad data. I'm on Informix 7.31 and sun 2.6. Thanks much,
>
> Michael Brizendine
> michaelb@pegsinc.com
>
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Michael Brizendine wrote: > > All, > > I am wondering if anyone knows a way to force the unload statement into > unloading the word null or something like that so that I can > differentiate a null value from a space or a 0 length string. With the > unload in its current state all the above values look like || when > unloaded... This is a maint. nightmare for me since we have some tables > with bad data. I'm on Informix 7.31 and sun 2.6. Thanks much, Actually unload unloads zero length strings as "| |" while NULL strings are unloaded as "||". There is a difference that you can recognize. If you are post processing perhaps that is where the distinction is being lost. Art S. Kagel
I don;t think thats a great idea. I know its a chance in a million but what if a field purposely contains 'any_string'. Unlikely I know but .....if it can happen it will. Peter Sloboda <petersloboda@earthlink.net> wrote in message news:3784C7A2.B84C1C7D@earthlink.net... > Hi, > I mean, the easiest way is to substitute nulls with 'any_string' in the > table, > unload it and then set it back to nulls > > Peter > (may be it's more complex as I think) > > Michael Brizendine wrote: > > > All, > > > > I am wondering if anyone knows a way to force the unload statement into > > unloading the word null or something like that so that I can > > differentiate a null value from a space or a 0 length string. With the > > unload in its current state all the above values look like || when > > unloaded... This is a maint. nightmare for me since we have some tables > > with bad data. I'm on Informix 7.31 and sun 2.6. Thanks much, > > > > Michael Brizendine > > michaelb@pegsinc.com > >