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
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).
Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
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
This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact