Chr(0) in Oracle Options

codeling Posts: 1089 Points: 4569
Posted: Wednesday, April 27, 2016 3:04:24 PM

CHR(0) is the ASCII character represented by 0. In C++ programming language, it means "end of string"  and is used to terminate strings.


codeling Posts: 1089 Points: 4569
Posted: Wednesday, April 27, 2016 3:09:49 PM

In some database tools, such as RapidSQL, anything to the right of chr(0) becomes invisible:

SELECT

'Hello' || CHR (0) || 'TOM' STRING,

LENGTH ('Hello' || CHR (0) || 'TOM') LEN,

SUBSTR ('Hello' || CHR (0) || 'TOM', 6, 4) STRG_1,

SUBSTR ('Hello' || CHR (0) || 'TOM', 7, 3) STRG_2

FROM DUAL

The output in the RapidSQL is:

STRING LEN STRG_1 STRG_2
Hello  9   TOM    TOM

 


codeling Posts: 1089 Points: 4569
Posted: Wednesday, April 27, 2016 3:16:30 PM

You can see the right result in SQLPlus because SQLPlus is a C program:

SQL> ed
Wrote file afiedt.buf

  1  SELECT
  2  'Hello' || CHR (0) || 'TOM' STRING,
  3  LENGTH ('Hello' || CHR (0) || 'TOM') LEN,
  4  SUBSTR ('Hello' || CHR (0) || 'TOM', 6, 4) STRG_1,
  5  SUBSTR ('Hello' || CHR (0) || 'TOM', 7, 3) STRG_2
  6* FROM DUAL
  7  /

STRING               LEN        STRG_1    STRG_2
-------------------  -------    --------- -----------
Hello TOM            9           TOM      TOM

codeling Posts: 1089 Points: 4569
Posted: Wednesday, April 27, 2016 3:20:08 PM
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
Users browsing this topic
Guest