At 06:23 PM 24/05/2005 -0500, you wrote:

>hi, all.
>
> I need to delete all the spaces (' ') character between words in a
>varchar()
> field.... May I do this?, How?
>
>for example: I need to transform this text 'Action [ 50 : 45 ]; Legal [
>90 ]' into 'Action[50:45];Legal[90]' without spaces

In SQL, no, there is no internal function that will strip a character out
of a string. You could search the various third-party UDF libraries to see
whether someone has written a UDF that does it, or you could write one
yourself.

Alternatively, you could write some PSQL to do it. It is not clear whether
your intention is to validate input (via a BEFORE INSERT OR UPDATE trigger)
or to repair data that already exists (or both?). Assuming the column uses
a single-byte character set, you could write an executable procedure that
could be invoked by triggers or by another SP, something like this:

create procedure StripBlanks (instring varchar(24) )
returns (outstring varchar(24))
as
declare currentchar char;
begin
currentchar = '?';
outstring = '';
while (currentchar = '?') do
begin
currentchar = substring(instring from 1 for 1);
if ( (currentchar <> '')
and (currentchar is not null) ) then
begin
if (currentchar <> ' ') then
outstring = outstring || currentchar;
currentchar = '?';
instring = substring(instring from 2);
end
end
end

Then, your trigger could be something like this:

create trigger biu_opinions for opinions
active before insert or update
as
declare newdoc_id varchar(24);
begin
if (new.document_id is not null) then
begin
execute procedure StripBlanks (new.document_id)
returning_values (:newdoc_id);
new.document_id = newdoc_id;
end
end

A "fix-up" procedure could be something like this:

create procedure FixDocumentIDs as
declare newdoc_id varchar(24);
begin
for select document_id from opinions for update
into :newdoc_id
as cursor c
do begin
execute procedure StripBlanks (:newdoc_id)
returning_values (:newdoc_id);
update opinions
set document_id = :newdoc_id
where current of c;
end
end

./hb