Recently came across a very stange behaviour of chr(0) as while inserts it behaves properly while in updates it actually updates the value with a space(chr(32)) instead of chr(0).eg.
create table t1(a char(10));
create table t2(a char(18));
insert into t1 values('0123456789');
commit;
insert into t2 select lpad(a,018,chr(0)) from t1;
commit;
alter table t1 modify(a char(18));
update t1 set a=lpad(a,018,chr(0));
commit;

SQL> select * from t1;

A
------------------
0123456789

SQL> select * from t2;

A
------------------
        0123456789

SQL> select replace(a,chr(0),'s') from t1;

REPLACE(A,CHR(0),'
------------------
0123456789

SQL> select replace(a,chr(0),'s') from t2;

REPLACE(A,CHR(0),'
------------------
ssssssss0123456789

Can you explain this please?
 

and how do i go about it?

Since you are using char(18) instead of varchar2(18), isn't the column padded with spaces when you do the alter-table (try "select replace(a,' ','.') from t1;" before the update)? In that case it isn't the updates fault because there isn't anything to pad. You might try "update t1 set a=lpad(trim(a),018,chr(0));"

Tom,

Please explain how to use 'dump' to show info ("use dump, sqlplus is a c program....")

Thanks,

Robert.

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 7 08:27:41 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> drop table junk;

Table dropped.

SQL> create table junk(string  varchar2(30));

Table created.

SQL> insert into junk values ('asdf'||chr(0)||'asdf');

1 row created.

SQL> commit;

Commit complete.

SQL> select string, dump(string) from junk;

STRING
------------------------------
DUMP(STRING)
--------------------------------------------------------------------------------
asdf asdf
Typ=1 Len=9: 97,115,100,102,0,97,115,100,102
 

..but SQL*Plus showing "ASK TOM" for 'ASK' || CHR (0) || 'TOM' ?

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Sep 19 16:51:51 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production



DB1> l
1 SELECT 'ASK' || CHR (0) || 'TOM' STRING,
2 LENGTH ('ASK' || CHR (0) || 'TOM') LN,
3 SUBSTR ('ASK' || CHR (0) || 'TOM', 4, 4) STRG_1,
4 SUBSTR ('ASK' || CHR (0) || 'TOM', 5, 3) STRG_2
5* FROM DUAL
DB1> /

STRING LN STRG STR
------- ---------- ---- ---
ASK TOM 7 TOM TOM

1 SELECT 'ASK' || chr(0) || 'TOM' STRING1,
2 'ASK' || chr(32) || 'TOM' STRING2
3* FROM DUAL
interspc@spex.NNVH023A> /

STRING1 STRING2
------- -------
ASK TOM ASK TOM

GOOD Discussion regarding Chr(0).

In the following queries, the first one returns columns with the separator specified. Whereas in the second one
the replace and CHR(0) does not allow the column separator to appear. Any reaons for this specific behaviour?
Query 1
=======
SET echo off pagesize 0 feed off head off term off verify off
SET colsep ','
SPOOL C:\test.txt

SELECT col1, col2, col3 FROM table_name1;

SPOOL off

Query 2
=======
SET echo off pagesize 0 feed off head off term off verify off
SET colsep ','
SPOOL C:\test.txt

SELECT replace(col1,chr(0),' '), replace(col2,chr(0),' '), replace(col3,chr(0),' ') FROM table_name1;

SPOOL off

ops$tkyte%ORA10GR2> @test
set termout on
!cat test.sql
drop table table_name1;
create table table_name1
as
select username col1, username col2, username col3 from all_users where rownum <= 5;

SET echo off pagesize 0 feed off head off term off verify off
SET colsep ','
spool x.lst
SELECT col1, col2, col3 FROM table_name1;
spool off

SET echo off pagesize 0 feed off head off term off verify off
SET colsep ','
spool y.lst
SELECT replace(col1,chr(0),' '), replace(col2,chr(0),' '),
replace(col3,chr(0),' ') FROM table_name1;
spool off

@connect /
!diff x.lst y.lst
!ls -l x.lst y.lst


-rw-rw-r--  1 tkyte tkyte 236 Feb  6 16:10 x.lst
-rw-rw-r--  1 tkyte tkyte 236 Feb  6 16:10 y.lst

Just found out that a chr(0) in a varchar2 string will generate an ORA-31011 Error using XML_FOREST. Who ist ever interessted in putting chr(0) in a string?


Or is it possible that on some systems a SYS_CONTEXT('USERENV', HOST') generates a chr(0) at the end (depending on OS or someting). This is the default value used on the field that has a chr(0) at the end.

SQL> select chr(0) from dual;

C
-


SQL> select nvl(chr(0), 'test') from dual;

N
-


SQL> select '|'||chr(0)||'|' from dual;

'|'
---
| |

Anything right to the chr(0) vanishes!!!
But why no in the above case?

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 7 05:54:54 2013

ops$tkyte%ORA11GR2> l
  1  SELECT 'ASK' || CHR (0) || 'TOM' STRING,
  2         LENGTH ('ASK' || CHR (0) || 'TOM') LN,
  3         SUBSTR ('ASK' || CHR (0) || 'TOM', 4, 4) STRG_1,
  4         SUBSTR ('ASK' || CHR (0) || 'TOM', 5, 3) STRG_2
  5*   FROM DUAL
ops$tkyte%ORA11GR2> /

STRING          LN STRG STR
------- ---------- ---- ---
ASK TOM          7  TOM TOM

ops$tkyte%ORA11GR2> 

As per all the earlier comments, be very careful when examining output which may contain CHR(0) and if things look odd then use DUMP to see what's really going on.


However I just thought I'd add that when using NVL to compare for [in]equality which is also comparing NULLS, CHR(0) is my first choice for character values, eg:

SELECT *
FROM table
WHERE NVL(char_column1, CHR(0)) <> NVL(char_column2, CHR(0));

That is useful knowledge of DECODE, thank you.

However it seems totally inconsistent with NULL handling in the rest of the database. Why is DECODE able to compare NULLs when everything else treats it as "cannot be known"?