This works for me on DB2 for iSeries. This is probably not the best way, but
I was able to throw it together in a few minutes, and it seems to work.

Here's a sample run:
select
anAlpha, IntegerPart(anAlpha)
from
deleteme

ANALPHA INTEGERPART
20alpha 20
20 20
twenty -
twenty20 20

Regards,
Dave

CREATE FUNCTION IntegerPart (
inString VARCHAR(100)
)
RETURNS integer
LANGUAGE SQL
modifies sql data
set option dbgview = *source

BEGIN
-- Scratch variables used for processing
DECLARE outputString VARCHAR(100);
DECLARE stringLength INT;
DECLARE loopCounter INT;
DECLARE charAtPos VARCHAR(1);

-- If the incoming string is NULL, return NULL
IF (inString IS NULL) THEN
return NULL;
End If;

-- Initialize the scratch variables
SET outputString = '';
SET stringLength = LENGTH(inString);
SET loopCounter = 1;

-- Loop over the string
WHILE (loopCounter <= stringLength) DO
-- Get the single character off the string
SET charAtPos = LOWER(SUBSTRING (inString, loopCounter, 1));

-- If the character is numeric, add it to the result
IF ((CharAtPos >= '0') and (CharAtPos <= '9')) then
SET outputString = outputString || charAtPos;
END IF;
SET loopCounter = loopCounter + 1;
ND WHILE;

-- Return the final output
if (outputString = '') then
RETURN NULL;
end if;

RETURN (integer(outputString));

END



The information contained in this e-mail message and any attachments is confidential and intended only for the addressee(s). If you are not an addressee, you may not copy or disclose the information, or act upon it, and you should delete it entirely from your email system. Please also notify the sender that you received this e-mail in error.
-----Original Message-----
From: thirumaran
[mailto:db2udbdba-ezmlmshield-x62947385.[Email address protected]
Sent: Friday, October 29, 2004 6:38 AM
To: LazyDBA Discussion
Subject: function that fetches the integer portion from string

Dear All,

I am looking for any function that fetches the integer portion from string
in DB2 UDB V 8.1

Eg:

Lets consider a function "fx"

12-Sales à So fx('12-Sales') should return 12.

fx('sales') should return either NULL or 0.



Thanks

Thirumaran






---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

DB2 & UDB email list listserv db2-l LazyDBA home page