Updating Hex Value in a Char Field
A user had a CHAR(1024) column containing embedded Unix line feeds, which broke the layout of unloaded flat files (one row appearing as several lines). He asked how to strip/replace the line feeds in the database or during unload. Gary Gu offered a sed one-liner to rejoin lines ending with the backslash continuation that UNLOAD writes. Jonathan Leffler noted standard LOAD handles those escaped newlines anyway, and showed an in-database fix: UPDATE tab SET col = REPLACE(col, CHR(10), ' '), using the CHR stored procedure from the IIUG Software Archive (ASCII is built in on 11.50).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Let me start by saying I can not do anything about the data before it gets in the table. I have a table that has a field char(1024). In this field there are some unix line feed characters. When this table is unloaded into a unix file system those line feed special characters of course cause issues. I have one record that has six in it and the file system then thinks it is six records in the file. Is there anyway to update a char field while the data is still in the database and change those hex line feed values to a hex space? They are randomly in the record. Is there a way to translate the data when it is unloaded using some type of expression before it hits the file system? The only thing I can see that works is unloading one records at a time, striping off all line feeds, adding a line feed to the end of the record, and then loading it back. This is very time consuming. Thanks in advance for any ideas
Do you mind emailing me a sample unload file with this data in it? I am fully following this but think I can help... jmmagie@yahoo.com Mike
e-mail sent
Most often you'll have lines with a trailing \\\\ in the unloaded file and can join them by a sed command like: sed ':a; /\\\\\\\\$/N; s/\\\\\\\\\\ //; ta' filename Gary --------------------------------------- Let me start by saying I can not do anything about the data before it gets in the table. I have a table that has a field char(1024). In this field there are some unix line feed characters. When this table is unloaded into a unix file system those line feed special characters of course cause issues. I have one record that has six in it and the file system then thinks it is six records in the file. Is there anyway to update a char field while the data is still in the database and change those hex line feed values to a hex space? They are randomly in the record. Is there a way to translate the data when it is unloaded using some type of expression before it hits the file system? The only thing I can see that works is unloading one records at a time, striping off all line feeds, adding a line feed to the end of the record, and then loading it back. This is very time consuming. Thanks in advance for any ideas
sorry my mistake: sed -e :a -e '/\\\\\\\\$/N; s/\\\\\\\\\\ //; ta' filename
Reviving a nearly moribund thread ... On Wed, Sep 17, 2008 at 6:29 AM, MARK DUNKER <mr_mark95@go.com> wrote: > Let me start by saying I can not do anything about the data before it gets in > the table. > > I have a table that has a field char(1024). > > In this field there are some unix line feed characters. When this table is > unloaded into a unix file system those line feed special characters of course > cause issues. I have one record that has six in it and the file system then > thinks it is six records in the file. Can you explain why this is a problem? The standard UNLOAD utilities format the data with a backslash before the embedded newline so that the standard LOAD utilities know that they need to continue onto the next line. > Is there anyway to update a char field while the data is still in the database > and change those hex line feed values to a hex space? They are randomly in the > record. Assuming a moderately recent version of IDS, yes. There's some code in the IIUG Software Archive that includes a stored procedure, CHR, that generates the character corresponding to a given number (the inverse of ASCII - which seems to be built into 11.50, unlike CHR). With that, I was able to use: UPDATE SomeTable SET ColumnWithNewLines = REPLACE(ColumnWithNewLines, CHR(10), ' '); Worked nicely on the one-row example table I had: + begin work; + select * from t37; multi\\\\ line\\\\ data\\\\ field + update t37 set c = replace(c, chr(10), ' '); + select * from t37; multi line data field + rollback work; > Is there a way to translate the data when it is unloaded using some type of > expression before it hits the file system? Various people pointed out various ways to do that. > The only thing I can see that works is unloading one records at a time, > striping off all line feeds, adding a line feed to the end of the record, and > then loading it back. This is very time consuming. Hard work, certainly. Do you really need the newline at the end in the DB? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.