Thank's Helen...
that's working right now.

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <firebird-support@yahoogroups.com>
Sent: Tuesday, May 24, 2005 6:40 PM
Subject: Re: [firebird-support] delete space (' ') in string field


> 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
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>