[This topic is migrated from our old forums. The original author name has been removed]
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
a
anonymous
said
over 10 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: @export with trim
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
Hans Bergsten
said
over 10 years ago
[This reply is migrated from our old forums.]
Re: @export with trim
Hi,
Great, I'm glad I could put you on a track to a solution.
Best Regards,
Hans
anonymous