Weird Output on SQL REPLACE

Question

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 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

UPDATE: I posted the answer below.

Answer 1

Try using NULL rather than an empty string. i.e. REPLACE(RCAPIN, ' ', NULL)

Answer 2

We ended up using concat and substring to get the results we wanted.

CREATE VIEW RLIC2GIS AS                                  
SELECT CONCAT(SUBSTR(RCAPIN,1,3),CONCAT(SUBSTR(RCAPIN,5,2),      
CONCAT(SUBSTR(RCAPIN,8,2), CONCAT(SUBSTR(RCAPIN,11,3),           
SUBSTR(RCAPIN, 15,3))))) AS CAPIN13, 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

Answer 3

The problem here might be that what you think is the blank character in that field is actually some other unprintable character.

You can use the following SQL to see what ASCII character is at the 4th position:

select ascii(substr(RCAPIN,4,1)) 
FROM YOUR-TABLE

Then you would be able to use a replace for that character instead of the blank space:

select replace(RCAPIN,chr(9))
FROM YOUR-TABLE