RE: Weird Output on SQL REPLACE

REPLACE as you have it will replace all hex 40 values (blanks) with nothing
(i.e. remove them out of the string output). Equivalent statement is
REPLACE(RCAPIN,x'40','').

I recommend you display your data with HEX function to verify if the
non-displayable character is indeed hex 40 or some other value. This will
alleviate the possibility of considering all non-displayable values as
blanks when running the query on green-screen.
Once you know what these values are, you can address them as well (i.e.
additional REPLACE call or perhaps TRANSLATE then REPLACE).

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Weird Output on SQL REPLACE

I am using REPLACE in an SQL view to remove the spaces from a property
number. The function is setup like this REPLACE(pin, ' ', ''). On the
green-screen the query looked fine. In anything else we get the hex values
of the characters in the field. I am sure it is an encoding thing, but how
do I fix it?

Here is the statement I used to create the view:
CREATE VIEW QMFILES/RLIC2GIS AS SELECT REPLACE(RCAPIN, ' ', '') AS
RCAPIN13 , RLICNO, RONAME, ROADR1, ROADR2, ROCITY, ROSTAT, ROZIP1,
ROZIP2, RGRID, RRADR1, RRADR2, RANAME, RAADR1, RAADR2, RACITY,
RASTAT, RAZIP1, RAZIP2, REGRES, RPENDI, RBLDGT, ROWNOC, RRCODE,
RROOMS, RUNITS, RTUNIT, RPAID, RAMTPD, RMDYPD, RRFUSE, RNUMCP,
RDATCP, RINSP, RCAUKY, RCAPIN, RAMTYR, RYREXP, RDELET, RVARIA,
RMDYIN, RDTLKI, ROPHN1, ROPHN2, ROCOM1, ROCOM2, RAPHN1, RAPHN2,
RACOM1, RACOM2, RNOTES FROM RLIC2