chr(0) works differently for insert and update
sushil, September 07, 2005 - 5:42 am UTC
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?
September 07, 2005 - 8:15 am UTC
use dump, sqlplus is a c program. dump will show you what is there.
sushil, September 07, 2005 - 8:57 am UTC
and how do i go about it?
A reader, September 07, 2005 - 9:34 am UTC
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));"
Please explain how to 'dump'
Robert, September 07, 2005 - 11:15 am UTC
Tom,
Please explain how to use 'dump' to show info ("use dump, sqlplus is a c program....")
Thanks,
Robert.
dump
A reader, September 07, 2005 - 11:43 am UTC
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
Thanks 'a reader'!
Robert, September 07, 2005 - 11:46 am UTC
SQL*Plus showing "ASK TOM" for 'ASK' || CHR (0) || 'TOM'
A reader, September 19, 2005 - 11:10 am UTC
..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
September 19, 2005 - 11:58 am UTC
depends on the sqlplus/os versions.
anything that uses c printfs would see a chr(0) and stop
'ASK' || chr(0) || 'TOM' = 'ASK' || chr(32) || 'TOM' ??
A reader, September 19, 2005 - 11:13 am UTC
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
Chr(0) - a null value
Naveen Kumar, October 12, 2006 - 11:52 pm UTC
GOOD Discussion regarding Chr(0).
CHR(0) and replace function
br, February 06, 2009 - 11:44 am UTC
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
February 06, 2009 - 4:16 pm UTC
give full example.
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
I am not able to observe any differences.
chr(0) in XML
Danil, May 05, 2009 - 12:30 pm UTC
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.
May 11, 2009 - 8:53 am UTC
and do you know what chr(0) represents in the programming language C?
end of string!
and chr(0) is an entirely invalid XML character, you cannot use it - it would not be and XML document if it contains that. You'd need to escape it.
Shankar S, June 07, 2013 - 11:05 am UTC
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
June 07, 2013 - 1:42 pm UTC
things change over time, they must have fixed the issue in sqlplus whereby if you had a string with a \0 in it - the routine they used to print would assume it was the end of the string (C is like that).
The data never disappeared, it was always there - it was simply a sqlplus printing issue.
in current releases of sqlplus:
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>
they are translating \0 into a space before printing it. sqlplus didn't do that in the past and it was fixed.
I use CHR(0) somewhat frequently
Andrew, June 12, 2013 - 10:46 am UTC
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));
June 18, 2013 - 3:05 pm UTC
just use decode in the future
where decode( char_column1, char_column2, 1, 0 ) = 0;
decode considers NULL's to be the "same". that way - no tricks are involved.
Most interesting, I didn't realise that
Andrew, June 21, 2013 - 10:09 am UTC
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"?
July 01, 2013 - 3:59 pm UTC
I think because DECODE is as old as dirt, predates any semblance of an ANSI standard by a good decade or more....
A reader, January 22, 2014 - 7:16 am UTC