In my companies table there's a CLOB column for products - basically free text with #32 replaced by #160 so each line acts like a separate word.

I'm setting up the delete part now eg

UPDATE Companies SET _Products  =  REPLACE('Junk Product'' WITH '' IN _Products) WHERE _Products LIKE '%Junk Product%'

followed by

UPDATE Companies SET _Products = NULL WHERE _Products = ''

Can anyone come up with a way of combining the two statements?

Roy Lambert

I've decided to go with an external function written in Delphi.

Roy Lambert

Roy

> I'm setting up the delete part now eg
>
> UPDATE Companies SET _Products = REPLACE('Junk Product'' WITH '' IN
_Products) WHERE _Products LIKE '%Junk Product%'
>
> followed by
>
> UPDATE Companies SET _Products = NULL WHERE _Products = ''
>
> Can anyone come up with a way of combining the two statements?

I know you are going to use an external function but in this case isn't it
just

UPDATE Companies SET _Products =  NULL WHERE _Products LIKE '%Junk
Product%'
AND REPLACE('Junk Product' WITH '' IN _Products)=''

John

John

>I know you are going to use an external function but in this case isn't it
>just
>
> UPDATE Companies SET _Products = NULL WHERE _Products LIKE '%Junk
>Product%'
> AND REPLACE('Junk Product' WITH '' IN _Products)=''

1. Performing an update as part of the WHERE clause! Is that allowed and does it work?

2. No because these are CLOB columns containing lists and getting rid of the #13#10 / NULLing the field is tricky

3. _Products might not = '' because there may well be products left in the list.

Roy Lambert

Roy

>
> 1. Performing an update as part of the WHERE clause! Is that allowed and
does it work?

Executing a function is allowed in a  WHERE clause (ie REPLACE returns a
string) but of course it isn't updating the non blank items (that's me just
having a senior moment .

> 2. No because these are CLOB columns containing lists and getting rid of
the #13#10 / NULLing the field is tricky

A bit tricky but do-able with a function.  I am sure the following can be
improved but it does seem to produce the correct result.  It expects the
"junk" lines to be #13#10 separated.

John

CREATE FUNCTION "striplines" (IN "linestostrip" CLOB COLLATE ANSI, IN
"indata" CLOB COLLATE ANSI)
RETURNS CLOB COLLATE ANSI
BEGIN
DECLARE temp clob;
DECLARE temp1 clob;
DECLARE test varchar;

SET temp=indata+#13+#10;
SET temp1=linestostrip+#13+#10;

WHILE temp1 <> '' do
 SET test= LEFT(temp1,position(#13+#10,temp1)+1);
 SET temp1 = substring(temp1,position(#13+#10,temp1)+2,LENGTH(temp1));
 IF test <> #13+#10 then
   SET temp= replace(test WITH '' in temp);
 END if;
END WHILE;

RETURN IF(LENGTH(temp) > 2 then LEFT(temp,LENGTH(temp)-2) ELSE null);

END

John


The function looks pretty neat, but, for me, I challenge the benefit of doing it in SQL rather than Delphi, mainly because I find Delphi more readable, easier to type and easier to format

I think it has something to do with being more used to Delphi.

The Delphi function I'm using is below (and I've posted my entire external functions code into extensions). It can be called in a number of different modes, and of course, nested to get the desired result eg

CLOBMerge(CLOBMerge(' + fld + ',' + ClobZap + ',''X''),' + QuotedStr(MergeID) + ' )'

which removes the ones I am going to replace, and adds in the one they're being replaced with, but only if not duplicated.

Roy Lambert

function CLOBMerge(const clob1, clob2, Mode: string): string;
var
sl: TStringList;
sl2: TStringList;
sl1: TStringList;
Cntr: integer;
idx: integer;
iMode: Char;
begin
{Mode can be M = merge as is, D = Dedup, S = no dedup but sort, B = must be in both, X not in clob 2}
iMode := UpperCase(Mode)[1];
if iMode in ['M', 'D', 'S', 'B', 'X'] then begin
 if (clob1 <> '') or (clob2 <> '') then begin
  sl := TStringList.Create;
  sl1 := TStringList.Create;
  sl2 := TStringList.Create;
  try
   if iMode in ['M', 'D', 'S'] then begin
    if iMode = 'D' then begin
     sl.Sorted := True;
     sl.Duplicates := dupIgnore;
    end else if iMode = 'S' then begin
     sl.Sorted := True;
     sl.Duplicates := dupAccept;
    end;
    sl1.Text := clob1;
    sl2.Text := clob2;
    for Cntr := 0 to sl1.Count - 1 do sl.Add(sl1[Cntr]);
    if (iMode = 'M') and (sl1.Count > 0) and (sl2.Count > 0) then sl.Add('');
    for Cntr := 0 to sl2.Count - 1 do sl.Add(sl2[Cntr]);
   end else if iMode = 'B' then begin
    if (clob1 <> '') and (clob2 <> '') then begin
     sl1.Text := clob1;
     sl2.Text := clob2;
     if sl1.Count > sl2.Count then begin
      sl.Assign(sl2);
      for Cntr := sl.Count - 1 downto 0 do begin
       if sl1.IndexOf(sl[Cntr]) = -1 then sl.Delete(Cntr);
      end;
     end else begin
      sl.Assign(sl1);
      for Cntr := sl.Count - 1 downto 0 do begin
       if sl2.IndexOf(sl[Cntr]) = -1 then sl.Delete(Cntr);
      end;
     end;
    end;
   end else if iMode = 'X' then begin
    sl.Text := clob1;
    sl1.Text := clob2;
    for Cntr := 0 to sl1.Count - 1 do begin
     idx := sl.IndexOf(sl1[Cntr]);
     if idx > -1 then sl.Delete(idx);
    end;
   end;
   Result := sl.Text;
  finally
   sl1.Free;
   sl2.Free;
   sl.Free;
  end;
 end else Result := '';
end else Result := '';
end;

Roy

> I challenge the benefit of doing it in SQL rather than Delphi, mainly
because I find Delphi more readable, easier to type and easier to format

Easier debugging is the main thing for me.  Actual performance - whknows



Your function looks good.  Don't know if it's deliberate but X mode only
deletes the first occurence of items in the delete list.

John

John

>> I challenge the benefit of doing it in SQL rather than Delphi, mainly
>because I find Delphi more readable, easier to type and easier to format
>
>Easier debugging is the main thing for me. Actual performance - whknows
>

Interesting, debugging I usually find easier in Delphi. Of course I have to set up a test bed for it.

>
>Your function looks good. Don't know if it's deliberate but X mode only
>deletes the first occurence of items in the delete list.

Your comment took me a while to work out. My first reaction was panic. I tested in Delphi and I was going to accuse you of going senile. My reading of your comment was I was only deleting the first item in clob2 but it eventually penetrated my ossifying brain that you mean if there are duplicate items in clob1 only the first will be zapped.

Its sort of deliberate ie it shouldn't be possible to populate the columns I was targeting with one than one instance in the list. But its a good idea so here's the fix

for Cntr := 0 to sl1.Count - 1 do begin
 idx := MaxInt;
 while idx > -1 do begin
  idx := sl.IndexOf(sl1[Cntr]);
  if idx > -1 then sl.Delete(idx);
 end;
end;

Roy Lambert

Roy

> >> I challenge the benefit of doing it in SQL rather than Delphi, mainly
> >because I find Delphi more readable, easier to type and easier to format
> >
> >Easier debugging is the main thing for me. Actual performance - whknows
> >
>
> Interesting, debugging I usually find easier in Delphi. Of course I have
to set up a test bed for it.

That is what I meant - I don't know how to effectively debug functions in
EDB at all.

> I tested in Delphi and I was going to accuse you of going senile.

Fair comment

John

John

>That is what I meant - I don't know how to effectively debug functions in
>EDB at all.

I needed help on that one - from Tim

<<Open the DLL project in the IDE, set its output directory to the
configuration path, and then set the Run/Parameters so that the host
application is the EDB Manager.  That's it.  Once you run (F9) the project
and the DLL is loaded by the EDB Manager, you'll see any breakpoints enable,
and any exceptions will stop on the line of code that is causing the
exception>>

Roy Lambert