Mass find & replace in table?

jdavis72 · Newbie Joined: 20 Feb 2006 Posts: 2

After importing a large item cost list from OOo Calc to Base, I see where all of the items that have double quotes (representing inches, as in 14" long) in them changed to a few odd characters. I can easily go into the table and delete and replace them with the correct double quotes, but is there a faster way? I don't see a Find & Replace choice in Base. The file is in the default OOo .odb file format, in WinXP. I found one solution after searching these forums, but I assumed they were for connecting to a larger DB (SQL, etc.). Thanks in advance!

Jeremy

bramkuijper · General User Joined: 03 Sep 2006 Posts: 5

good one. looking for that one too. Unfortunately, such a simple function has apparently been forgotten. Not there.

DrewJensen · Posted: Sun Sep 03, 2006 2:13 pm Post subject:

Sure you can do this.

You will have to SQL - or perhaps a macro.

First some simple changes.

make yourself a test table to get the hang of this
Table1( ID IDENTITY, DATA VARCHAR(50))
would work just fine.

Add some records

If I wanted to change all the entries in a column to upper case I could use this command in the SQL window. 'Tools>SQL'

UPDATE "Table1" SET "DATA" = UCASE("DATA" )

All text in the column DATA is now uppercase.

Now what about replacing data...yup there is a REPLACE function.

I can replace all occurrances of A with *, with this statement

UPDATE "Table1" SET "DATA" = REPLACE( "DATA", 'A', '*' )

You can find a full list of supported text functions in the HSQL users manual, downloadale from http://hsqldb.org

You can also find them listed in this spreadsheet
http://www.paintedfrogceramics.com/OpenOffice/hsql_functions.ods

If you need help with the actual update statement, let me know and I will try to help

HTH

Drew

remdelyon

Hi Drew, (or anybody else ? )

a question, you are saying that this code (which works properly in the SQL window) can be also used in a macro. I tried but it does not work, probably because the SQL does not know that the code must be applied to this database.

I searched everywhere but I was not able to find the "magic" statement to write in the begining of my macro to solve the problem (it probably looks like "CreateUnoService("com.sun.star.sdb.DatabaseContext") " or sthg like this...

I would be so glad to get the answer....
_________________
Ubuntu / Vista - LO 3.4.3

remdelyon · Posted: Fri Oct 20, 2006 1:25 pm Post subject:

OK, found.

If anybody wants the same information, the following example can help ( for a query counting the items in table "Vue1"... )