Unloading Null Values, 0 length strings and spaces...
Posted in 1999
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.
Topics: Migration, Import/Export & Data Conversion
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 > >