Re: find the longest word in a sentence with SQL

David,

Here is a "quick and dirty" UDF. Maybe it is not very efficient but, at
least, works with V5R3. Feel free to improve on it.

------------------------------
Drop Function qgpl/MaxWord;

Create Function qgpl/MaxWord (InputChar Char(1024))
Returns integer
Language SQL
Returns null on null input
Deterministic

Begin
Declare StringVar VarChar(1024);
Declare Delim VarChar(1);
Declare maxLen Integer;
Declare WordLen Integer;
Declare Pos1 Integer;
Declare Pos2 Integer;
Declare StrLen Integer;

Set Delim = ' '; --Just one blank space;
Set Pos1 = 1;
Set maxLen = 0;

-- Make sure that are not extra spaces in the string;
-- blank quotes: 1 space, empty quote (no spaces), 1 space
Set StringVar = Trim(
replace(replace(replace(Trim(InputChar),' ','<>'),'><',''),'<>',' ')
);
Set StrLen = Length(StringVar);

Repeat
Set Pos2 = Locate(Delim, StringVar, Pos1);

If Pos2 <> 0 THEN
Set WordLen = Pos2 - 1;
Set maxLen = max(maxLen, WordLen);
ELSE
-- Last Word!!
Set WordLen = (StrLen - Pos1) + 1;
Set maxLen = max(maxLen, WordLen);
END IF;

Set Pos1 = Pos2 + Pos1;
Until Pos2 = 0 END REPEAT;

Return maxLen;
End
------------------------------

Regards,


Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Mon, Nov 9, 2009 at 11:30 AM, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:

David,

Maybe an UDF? Also, If you have at least V5R4 you can use recursive
CTEs, so maybe something could be done with that technique (sorry, I'm
afraid that the powers-to-be decided to let our iSeries die with V5R3,
so I can't give you much more insights than those :-( ).


Regards
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries



On Mon, Nov 9, 2009 at 11:01 AM, David FOXWELL <David.FOXWELL@xxxxxxxxx>

wrote:

I don't think this could be done without a program.
Could it?
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.