I am trying to replace or remove the “ - “ character from read data in DB2/400 IBM System i mid-frame. Its very common in MS-SQL to make replacement using REPLACE function but here in DB2/400 V5R2 it seems that this function is not available. Could anybody help me in this regard?
Lightly tested on v5r3, the following is a somewhat functional User Defined Function (UDF) version of a [for lack of a DB2/400 SQL-supplied] REPLACE scalar on v5r2; the term "somewhat" meaning, that the defaulted mixed vs SBCS and CCSID for typing along with the specified lengths limiting the varying character string inputs and return value, though those can be adjusted by the invoker of the CREATE FUNCTION according to the needs of whomever will invoke the previously created REPLACEX scalar UDF:
CREATE FUNCTION REPLACEX /* private version of REPLACE() */
( SRC_STR VARCHAR(5000) /* source string; for mixed data or ccsid 1208? */
, FND_STR VARCHAR( 100) /* search string */
, RPL_STR VARCHAR( 100) /* replace-with string */
) RETURNS VARCHAR(8000) /* return string; for mixed data or ccsid 1208? */
LANGUAGE SQL
SPECIFIC REPLACEX
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
ALLOW PARALLEL
SET OPTION SRTSEQ=*HEX
, DECMPT=*PERIOD
, DBGVIEW=*SOURCE
BEGIN
declare strpos int default 1 ; /* start position for locate */
declare nxtpos int default 0 ; /* next "found" position */
declare fndlen int default 0 ; /* length of fnd_str */
declare rtnstr varchar(8000) default '' ; /* result-string */
set nxtpos = locate( fnd_str, src_str, strpos ) ;
set fndlen = length( fnd_str ) ;
while ( nxtpos > 0 ) do
set rtnstr = rtnstr
concat substr( src_str, strpos, ( nxtpos - strpos ) )
concat rpl_str
;
set strpos = nxtpos + fndlen ;
set nxtpos = locate( fnd_str, src_str , strpos ) ;
if ( nxtpos > 0 ) then /* adjust per *start* spec on LOCATE */
set nxtpos = nxtpos + strpos - 1 ;
end if ;
end while ;
set rtnstr = rtnstr
concat substr( src_str, strpos )
;
return
rtnstr
;
END