RE: Weird Output on SQL REPLACE

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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