@export with trim

Hello, I'm evaluating DBvisualizer and so far I'm very please with how it works. I'm having a trouble doing the @export command, all works fine, but I'm using it to migrate from one DB to another, to be specific, from iSeries' DB2 to Oracle. DB2 in it's nature is storing strings with empty spaces all the time, and I need them trimmed when I insert them into Oracle. I found I can do it with a PL/SQL but I export many tables I don't want to do it for every table. The complex is that I always export entire tables using "select * from..." that means I don't specify fields so I cannot use the trim() neither. Does anybody have a solution for this or an alternative way? Using the export from grid is not valid, since I have tables with lots of data.

Hi, The only thing I can think of in DbVisualizer that can help you with this would be to do a Replace All with regular expressions. Assuming you want to trim the trailing spaces in the string literals in an INSERT statement like this: INSERT INTO MyTable VALUES('A ', 1, 2, 3, 'B ', 'C ') Open the Edit->Replace dialog and enter this in Find what: ('{1}?.*?)\s+('{1}?) and this in Replace with: $1$2 Enable Use Regular Expressions and click Find and then All. NOTE! You should test this with your own data before using it on production data. I have only tested with a small sample and cannot guarantee that it handles all possible cases. Best Regards, Hans

Although I wished it could be solved within DBvisualizer, your answer made me think outside the box. I'm not good with regex and the file I generated is about 200MB, so open it with a text editor (or even DBvisualizer) will eat my memory. But thanks to your answer I end up with the unix command "sed" so I put it like this:

sed -e "s/ *'/'/g" test.sql > out.sql

(after the "s/" is with a blank space) Thank you very much Hans. Edited: And by the way, your solution works fine for small scripts !! Thank you again. Edited by: serchgt on May 15, 2014 3:26 AM

Hi, Great, I'm glad I could put you on a track to a solution. Best Regards, Hans