how to locate cr-lf character on AS400

[ddiamond]

I need to write a UDF that removes the end line characters from a string. On DB2 8.2, this would be easy because I could scan the string for chr(13) and chr(10). Unfortunately, DB2 on the AS400 does not have the chr function. Does anyone know how else I can do this on the AS400.

BTW - I've alreaded written my own Replace function for the AS400 - AS400 also lacks that one.

CREATE FUNCTION DDIAMOND.udfReplace ( 
	Source varchar(255),
	Find varchar(255),
	Replace varchar(255) ) 
	RETURNS varchar(255)   
	LANGUAGE SQL 
	SPECIFIC DDIAMOND.udfReplace 
	DETERMINISTIC 
	READS SQL DATA 
	RETURNS NULL ON NULL INPUT 
	NO EXTERNAL ACTION 
	NOT FENCED 
BEGIN ATOMIC
  DECLARE s varchar(255) DEFAULT '';
  DECLARE iStart int DEFAULT 1;
  DECLARE i int;
  
  set i = locate(upper(Find), upper(Source), iStart);
  
  WHILE i>0 DO
    set s = s || substr(Source, iStart, i - iStart) || Replace;
    set iStart = i + length(Find);
    set i = locate(upper(Find), upper(Source), iStart);
  END WHILE;
  
  return s || substr(source, iStart);
END

 

[ddiamond]

Alright,

I've discoved you can specify a string constant with the following notation: X'xx' where xx is a hexidecimal number. The only catch is I now need to know what the hexidecimal code is for a charage return on the AS400. Any suggestions?

 

[ddiamond]

I figured it out. The AS400 is using EBCDIC.

So Charage Return would be X'0D' and LF would be X'25'
and CRLF would be X'0D25'.