Re: Find and replace in OObase

On Wed, Apr 14, 2010 at 3:29 PM, venicite
<[email protected]>wrote:

>
> Excellent!  Works well for single item entries, but what about, in the
> following example, if I want to change "Darkwood" to "Dark Wood" but the
> table allows multiple keywords per record in the 'keyword' TextColumn?
> "Darkwood" is in many records, but with different other keywords following
> it:
> TextColumn for Record 37 (just a random #) contains
> "Darkwood,Kitchen,Island"
> TextColumn for Record 181 contains "Darkwood,Island,Burners", etc.
> There are a couple thousand records - do I have to replace all 'Darkwoods'
> by hand, or is there some sort of wildcard?
> Thanks!
>
>
>
>
Hi,

Given a table, Table1 and a column, txt, then

UPDATE "Table1" set "txt" = REPLACE( "Table1"."txt", 'Darkwood', 'Dark Wood'
) WHERE locate('Darkwood', "Table1"."txt") <> 0

You can find references for the SQL functions in any number of locations,
one of which is
http://wiki.services.openoffice.org/wiki/Built-in_functions_and_Stored_Procedures#String_Functions

HTH

Drew