It's HERE! SQL Built-In Functions and Stored Procedures : the i5/iSeries Programmer's Guide.. An introduction to creating and using SQL functions and stored procedures under OS/400 and i5/OS, this book explains how each is used and explores some of the stored procedures and functions included with SQL on the iSeries as part of OS/400. Programmers are lead through the development of custom-built functions for the programmer's own use,
$49.95

Note:V5R3 now has native support for this function within the REPLACE function.

StripChar SQL Function

When using Structured Query Language, you may find you need to remove a character from a character field. Since the iSeries DB2 UDB implementation of the SQL TRANSLATE function doesn't allow you to replace a character with NULL this isn't possible...Or is it. The Function shown below will accepts two parameters. The first is a type VARCHAR with a maximum length of 50 characters and is used to provide the string to be searched. The second is a type VARCHAR and a length of 1 and defines the search character. The value returned will be the original string with the search character removed.
This function would be used in an SELECT statement as shown below;
SELECT STRIPCHAR(Field1, "#") FROM MYLIB.MYFILE

Assuming that the value of Field1 was "ABC#123#4" this function would return "ABC1234" The Stripchar function can be created using the SQL CREATE FUNCTION statement shown below.

CREATE FUNCTION QGPL.STRIPCHAR(STRINGIN VARCHAR(50),           
CHR VARCHAR(50)) RETURNS VARCHAR(50) LANGUAGE SQL                   
BEGIN                                                               
      DECLARE X INT;                                                
      DECLARE RET VARCHAR(50);                                      
      SET X=1;                                                      
      WHILE X <= LENGTH(TRIM(STRINGIN)) DO                          
      IF SUBSTR(STRINGIN,X,1) != CHR THEN                           
      IF X = 1 THEN                                                 
      SET RET=SUBSTR(STRINGIN,X,1);                                 
      ELSE SET RET=TRIM(RET)||TRIM(SUBSTR(STRINGIN,X,1));           
      END IF;                                                       
      END IF;                                                       
      SET X=X+1;                                                    
      END WHILE;                                                    
      RETURN TRIM(RET);                                             
END

Either select the text above and copy it to the clipboard and paste it into SEU or click here to download the source in a text file.