Non-printable characters often find their way into the database. This often occurs due to EDI and manual copy/paste data entry from sources such as web-pages.

These intanace of “bad-data” can be dealt with using the translate built in function (%xlate). This could take place as a scheduled data cleansing exercise or be added to the programs which update the database.

The example below shows all characters with a hex value less than 40 being replaced by a space. The hex value 41 which represents a non-breaking space (NBSP) is also replaced.

d nonprnt         c                   x'000102030405060708090A0B0C0D0E0F-  
d                                       101112131415161718191A1B1C1D1E1F-  
d                                       202122232425262728292A2B2C2D2E2F-  
d                                       303132333435363738393A3B3C3D3E3F-  
d                                       41'                                
                                                                           
d space           c                    '                                -  
d                                                                      '   

 /free

   clnSurname = %xlate(nonprnt:space:surname);

 /end-free