CREATE PROCEDURE ADV_PART_SEARCH (
ARA VARCHAR(20))
RETURNS (
ID INTEGER,
ADI VARCHAR(50),
PART_NO VARCHAR(20))
AS
DECLARE VARIABLE FIRSTREC INTEGER;
DECLARE VARIABLE DEG VARCHAR(20) CHARACTER SET WIN1254;
DECLARE VARIABLE SQL VARCHAR(200) CHARACTER SET WIN1254;
begin
firstrec=1;
sql='select p.id,p.adi,p.part_no from parts p where (1=1)';
for select deger from p_splitstring(' ',:ARA) into :DEG
do
begin
if (firstrec=1) then
sql=sql || ' and (p.adi like '''||:DEG||'%'')';
else
sql=sql || ' and (p.adi like ''%' ||:DEG|| '%'')';
firstrec=0;
end

for execute statement sql into :ID,:ADI,:PART_NO do
suspend;
end

CREATE PROCEDURE P_SPLITSTRING (
AYIRAC VARCHAR(1),
METIN VARCHAR(355))
RETURNS (
DEGER VARCHAR(10))
AS
DECLARE VARIABLE P INTEGER;
DECLARE VARIABLE SEP VARCHAR(1);
begin
if (AYIRAC is null) then
AYIRAC=' ';
--SON KARAKTER AYIRAC DEGILSE AYIRAC EKLENIR
if (AYIRAC=' ') then
METIN=rtrim(METIN) || ' ';
else
if (cast(SUBSTR(:METIN,STRLEN(METIN),1) as varchar(1))<>AYIRAC) then
METIN=METIN || AYIRAC;


select DONEN from POS(:AYIRAC,:METIN) into :P;
while(P <> 0) do
begin
--string ayıklanır
DEGER= SUBSTR(:METIN,1,:P-1 );
--ayıklanan string cıkartılır.
METIN=SUBSTR(:METIN,:P+1,STRLEN(:METIN));
--ayırac tekrar aranır
select DONEN from POS(:AYIRAC,:METIN) into :P;
suspend;
end
end

sqlstr='select x.id,x.parcaadi, X.newprice '||
'from tablom X where (1=1)';

for select q from p_splitstring(' ',:PARCAENG) into :DEG do
sqlstr=sqlstr || ' and (x.parcaadi like ''%'||:DEG||'%'')';
sqlstr=sqlstr || ' order by x.parcaadi';