blanks to null

I need to move data from one table to other. basically I am doing insert into new_table select fld1, fld2, .... from old_table where .... now one of the char columns in old_table has many blank data. I want to insert it to the new_table as null when blank. How can I accomplish this in the select statement itself. I tried to use DECODE function but is not working. any pointers. TIA.

Just create a stored procedure, which returns NULL, if the orginal value contains only blanks. triggerfish2001@hotmail.com schrieb: > > I need to move data from one table to other. basically I am doing > insert into new_table > select fld1, fld2, .... > from old_table > where ....> > now one of the char columns in old_table has many blank data. I want to insert > it to the new_table as null when blank. How can I accomplish this in the > select statement itself. I tried to use DECODE function but is not working. > > any pointers. > > TIA. -- ******************************************************************************* * Any opinions written in this e-mail are my own. They may not correspond to * * those of my company. * ******************************************************************************* * Wolfgang Zager Berghauser Str. 104 * * BAB Data-Systems GmbH D-42349 Wuppertal * * Abt. Programmierung Tel. : +49 202 479870 * * Fax : +49 202 470035 * * E-Mail: zager@gmx.de * *******************************************************************************

Wolfgang says... >Just create a stored procedure, which returns NULL, if the orginal value >contains only blanks. yup I did the same already. However I think stored procedures are not as efficient as using informix functions like DECODE,NVL etc.

triggerfish2001@hotmail.com wrote in message <90r19r017ov@drn.newsguy.com>... >Wolfgang says... > >>Just create a stored procedure, which returns NULL, if the orginal value >>contains only blanks. > >yup I did the same already. However I think stored procedures are not as >efficient as using informix functions like DECODE,NVL etc. Now you are treading on thin ice... I think you may be about to provoke an argument on this newsgroup. For me, such a complaint about "efficiency" is completely pointless and naive. Do you want to get the job done, or do you want to be efficient? Have you actually measured the performance of the load with and without the stored procedure call? Compared it to having a separate update run?