Rich Pinder wrote:

> Of course, NOW I realize why I was looking for something 'built in' !!
>
> Read thru Helen's chapters, on both SP and Triggers, and continue to
> miss something (probably) quite easy.
>
> So, started with the SP described in SI's post below - description is:
>
>> CREATE OR ALTER PROCEDURE SPLITTEXT
>> (
>> ipTEXT VARCHAR(32000),
>> ipSPLITCHARS VARCHAR(20),
>> ipIGNORECHARS VARCHAR(20)
>> )
>> RETURNS
>> (
>> opTEXT VARCHAR(500))
>> AS
>> DECLARE VARIABLE vCurrChar VARCHAR(1);
>> DECLARE VARIABLE vIDX INTEGER;
>> DECLARE VARIABLE vLen INTEGER;
>>
>
> Example shows:
>
>> SELECT OPTEXT
>> FROM SPLITTEXT('+49/()6849-22415',NULL,'-+/()')
>>

I can't find the parent message on this thread that shows all the code..

> and this works fine. So SP is active.
>
> But, I'm fuzzy on how to pass parameters either in a) sql

I don't know if I understood you... but this example "SELECT OPTEXT FROM
SPLITTEXT('+49/()6849-22415',NULL,'-+/()')" *is* SQL

> (think I can't
> ), or b) inside a trigger (think I can, but just cant quite grasp it !!)
>

you could.

> I'm 'recoding' existing data, and want to insure new data goes in
> clean. On on INSERT and UPDATE, tried something like:
>
>
>> begin
>> new.phone = splittext(old.phone, NULL, '-');
>> end
>>
>
>

you are using bad syntax here...

Stored Procedures are not Stored Functions (not yet :-) ) to be used
like your example

try those variations (I don't know the procedure code, so I don't know
if it's selectable or executable):
Selectable (I think you have this one):
begin
select optext from splittext(old.phone, NULL, '-') into new.Phone;
end;

Executable:
begin
execute procedure splittext(old.phone, NULL, '-') returning_values
new.Phone;
end;


I had never did this kind of procedure inside triggers, but I think the
above syntax would be ok. You could try store the SP result into a
variable first and after that apply the variable value to the new.phone
field like thi:
.
Selectable (I think you have this one):

declare variable wPhone varchar(20) /* define it according to the column
definition of your table */

begin
select optext from splittext(old.phone, NULL, '-') into :wPhone;

new.Phone = wPhone;
end;

Executable:

declare variable wPhone varchar(20) /* define it according to the column
definition of your table */

begin
execute procedure splittext(old.phone, NULL, '-') returning_values
wPhone;

new.Phone = wPhone;
end;

> I am surely missing syntax here... and thanks for any points
>
> Rich
>
>
>

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br