I store account statements from a bank in a table and extract information from this.
In many of these statements there are blanks in the text, sometimes more than 10 in a row.
The number of blanks is different in each record.

I would like to reduce alle blanks to only one space.
Of course I can use a loop like this:

update kontoauszug set Zweck = REPLACE( '  ' WITH ' ' IN Zweck);
update kontoauszug set Zweck = REPLACE( '   ' WITH ' ' IN Zweck);
etc.

But is there a more effective way to do it?

Thanks. Uli

Uli,

1.
You can loop only the first statement, until there are no updated rows:
 update kontoauszug set Zweck = REPLACE( '  ' WITH ' ' IN Zweck);

2.
If you know the maximum number of possible blanks (and if it's a small
number), you can execute the sequence of statements you wrote, but I
think they will execute fast in reverse order, at least for some cases.
For example:

 update kontoauszug set Zweck = REPLACE( '    ' WITH ' ' IN Zweck);
 update kontoauszug set Zweck = REPLACE( '   ' WITH ' ' IN Zweck);
 update kontoauszug set Zweck = REPLACE( '  ' WITH ' ' IN Zweck);


--
Fernando Dias
[Team Elevate]

Fernando,

> You can loop only the first statement, until there are no updated rows:
>  update kontoauszug set Zweck = REPLACE( '  ' WITH ' ' IN Zweck);

Ok, that's clear.

> If you know the maximum number of possible blanks (and if it's a small
> number), you can execute the sequence of statements you wrote, but I think
> they will execute fast in reverse order, at least for some cases.

I see. The question is if that's really faster. In either way it seems that
all rows have to be scanned.

Thanks. Uli

Uli

> I see. The question is if that's really faster. In either way it seems
> that all rows have to be scanned.

Yes, I agree.
A better solution would be to write a function to remove the blanks.

--
Fernando Dias
[Team Elevate]

Fernando,

> A better solution would be to write a function to remove the blanks.

Could you explain that further?

Regards Uli

Uli

> Could you explain that further?

First, you must create a function that removes double blanks from strings:

---------------------------------
CREATE FUNCTION "RemoveBlanks" (IN "st" VARCHAR(512) COLLATE ANSI)
RETURNS VARCHAR(512) COLLATE ANSI
BEGIN
  Declare s Varchar(512);
  Set s = st;
  While Position('  ' In s) > 0 Do
    Set s = Replace('  ',' ',s);
  End While;
  Return s;
END
---------------------------------

You may also define an external Delphi function, instead; I don't know
if that would execute faster; I never tested the relative performance of
PSM functions vs. external functions.

Then you just use the function in an sql update statement:
----------------------------------
  update kontoauszug set Zweck = RemoveBlanks(Zweck);
----------------------------------



--
Fernando Dias
[Team Elevate]

Fernando,

> CREATE FUNCTION "RemoveBlanks" (IN "st" VARCHAR(512) COLLATE ANSI)
> RETURNS VARCHAR(512) COLLATE ANSI
> BEGIN
>   Declare s Varchar(512);
>   Set s = st;
>   While Position('  ' In s) > 0 Do
>     Set s = Replace('  ',' ',s);
>   End While;
>   Return s;
> END

That's really good idea. Thank you!

Regards Uli

Fernando,

<< First, you must create a function that removes double blanks from
strings: >>

Nice job.  One addition - you can just set the IN parameter and RETURNS as
VARCHAR and don't have to specify a length.  This is especially useful for
functions whereby you have no idea how long the incoming string will be, nor
do you know how long the resultant string will be.

--
Tim Young
Elevate Software
www.elevatesoft.com