VARCHAR2s that contain ASCII 0s

• Quoting Peter Grant to All dated 10-19-94 ***
  > Is there any way in SQL*Plus to manipulate VARCHAR2 columns that
  > contain ASCII 0s? I suspect they were inserted with Pro*C or Pro*FORTRAN,
  > but none the less, they are in the database.
  

Your best solution is NOT to put them in the database. Generally this happens in Pro*C because of a misunderstanding of how to insert strings properly into the database. Lazy and ignorant programmers often neglect to pad the entire string with SPACES as they should. (This is well documented.) That being said, let's address your immediate problem.

> I can't use REPLACE or TRANSLATE because I can't reference ASCII 0.
> The TO_CHAR function returns 48 for a TO_CHAR(0). When I use the SQL*Plus
> ASCII function to look at the characters, they show up as 0.

TO_CHAR is not the correct function to use. TO_CHAR converts a numeric value to its character equivalent; in other words, it will convert the number 0 to the character string ' 0' (the leading space is reserved for the unary.)

The function which will properly return ascii 0 for a value of zero is CHR(0), and its inverse is ASCII('?'). Using these functions, you can construct a replace or translate phrase in SQL. Received on Sun Oct 23 1994 - 15:08:44 CET