For purposes of setting report columns widths I want to find out the longest piece of text in each column. No problem for simple fields but does anyone have a suggestion for finding the longest string in a CLOB. I don't want the overall length of all the text in the field but the longest string eg

CLOB is
One
Two
Three
Four
Five
Six
Seven
Eight
Nine
Ten

The answer should be 5

Roy Lambert

Roy
> For purposes of setting report columns widths I want to find out the
longest piece of text in each column. No problem for simple fields but does
anyone have a suggestion for finding the longest string in a CLOB. I don't
want the overall length of all the text in the field but the longest string
eg
>
> CLOB is
> One
> Two
> Three
> Four
> Five
> Six
> Seven
> Eight
> Nine
> Ten
>
> The answer should be 5

How about a function like

CREATE FUNCTION "LongestString" (IN "InString" CLOB COLLATE ANSI)
RETURNS INTEGER
BEGIN
DECLARE i,highest INTEGER;
DECLARE st CLOB;

SET st = InString;
SET highest = 0;
WHILE pos(#13+#10,st) > 0 DO
 SET i = pos(#13+#10,st)-1;
 IF highest < i THEN
   SET highest = i;
 END IF;
 SET st = RIGHT(st,LENGTH(st)-POS(#13+#10,st)-1);
END WHILE;
SET i = LENGTH(st);
IF highest < i THEN
 SET highest = i;
END IF;
RETURN highest;
END

John

John


Nice and works well.

Roy Lambert

John,

You're like an SQL robot - plug in your request and out pops the SQL.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com