Non-printable sign
Hello, I have problem with replacing non-printable 'new line' sign (^J) to null. Actually, I don't know if Informix provides function like Oracle's ord() or ascii() which would be helpful. My task is to unload rows from sql query into file with default delimiter '|'. It is very important that each record is written in one separate line. However, it happens that one field can contain the 'new line' sign. In this case my row is split into two lines which is not allowed. The interested field is varchar type. Does anybody solved this kind of problem and how? Best regards Michal Demyda
DeMi wrote: > Hello, > > I have problem with replacing non-printable 'new line' sign (^J) to > null. Actually, I don't know if Informix provides function like > Oracle's ord() or ascii() which would be helpful. > My task is to unload rows from sql query into file with default > delimiter '|'. It is very important that each record is written in one > separate line. However, it happens that one field can contain the 'new > line' sign. In this case my row is split into two lines which is not > allowed. The interested field is varchar type. > > Does anybody solved this kind of problem and how? > > Best regards > Michal Demyda You should be able to strip these with tr and sed in a script. Informix has ascii(), but this would require retrieving of this field, cleaning it up and then output to file. You can do this is 4GL HTH Michael
On May 26, 3:18 pm, Michael Krzepkowski <mkrzepkow...@gmail.com> wrote: > DeMi wrote: > > Hello, > > > I have problem with replacing non-printable 'new line' sign (^J) to > > null. Actually, I don't know if Informix provides function like > > Oracle's ord() or ascii() which would be helpful. > > My task is to unload rows from sql query into file with default > > delimiter '|'. It is very important that each record is written in one > > separate line. However, it happens that one field can contain the 'new > > line' sign. In this case my row is split into two lines which is not > > allowed. The interested field is varchar type. > > > Does anybody solved this kind of problem and how? > > > Best regards > > Michal Demyda > > You should be able to strip these with tr and sed in a script. > > Informix has ascii(), but this would require retrieving of this field, > cleaning it up and > then output to file. You can do this is 4GL > > HTH > > Michael If the data is already in the database, then you'll need to select the bad records and replace them with the stripped out characters. Oracle 10g and later has a regex function which you could use to find non-printable "white space" within a string. (Your EOL, Ret, etc ...) I believe there is a regex function in IDS, if not, you can write one in C or Java as a UDR/UDF that could be used to strip out the data via an SQL statement, or you could have a before insert trigger call the function passing in the field and then returning the cleansed data to be inserted. The reason I would recommend this over a sed/awk or python script is that by putting this in the engine, you allow it to be used by other applications or non-sever centric sources of data. (An example would be allowing the app to be run on a pc and the data is local to the pc and not the server.) HTH -G
On May 26, 2:23 am, DeMi <michal.dem...@gmail.com> wrote: > I have problem with replacing non-printable 'new line' sign (^J) to > null. Actually, I don't know if Informix provides function like > Oracle's ord() or ascii() which would be helpful. > My task is to unload rows from sql query into file with default > delimiter '|'. It is very important that each record is written in one > separate line. However, it happens that one field can contain the 'new > line' sign. In this case my row is split into two lines which is not > allowed. The interested field is varchar type. > > Does anybody solved this kind of problem and how? It strikes me that replacing the newlines with ASCII NUL '\\0' would not be a good idea; that would mark the end of the string and effectively truncate anything after the newline. You would probably be better of replacing it with a blank. For mechanics, you should look at the REPLACE function - assuming you have a sufficiently recent version of IDS. There is also an ASCII function in IDS (11.50 at least, but I believe at least some earlier versions). There doesn't seem to be an ORD() or CHR() function built in -- odd, I thought it was added at the same time as ASCII (it should have been). If you poke around the IIUG web site, you should find a package ascii.tgz containing a now redundant ASCII function and a still relevant CHR function plus the data for a table which those functions use - and a script to assemble it all. (Contents: ascii.sql, ascii.unl, asciitbl.sql, chr.sql, mkascii.sql) If you really can't find that after some searching, contact me. You'd then use CHR(10) in the REPLACE function search string. There are probably other ways of doing it too. -=JL=-