substr, substrb and raw datatype

From: Rafal Dabrowa <rdsbrows_at_poczts.onet.pl>
Date: Tue, 14 Jun 2005 17:14:23 +0200
Message-ID: <d8msi1$3m7$1@nemesis.news.tpi.pl>

I have noticed that a column with "raw" datatype may be passed to substr and substrb functions instead of char table. But, despite Oracle documentation says that the functions are the same for single-byte database character set, these functions behave differently with raw column type.

Let's say, I have the following table:

      Name         Type
      ------------------------
      ID           NUMBER(38)
      VAL          RAW(20)

and I have one row in the table:

        ID VAL
     ----- --------------------
         1 0102030405060708090A

The following statement:
     select id, substr(val, 5, 2) from xyz;

gives the following output:

        ID SU
     ----- --
         1 03

But, when I replace "substr" by "substrb", output is the following:

        ID SUBS
     ----- ----
         1 0506

Why ? What is reason for the output difference ? Do I perform something forbidden ? Do all Oracle servers behave in the same way here, or maybe is it an "undefined" behavior ?

-- 
Rafal

sed -e s/s/a/g <my_address >my_right_address

Received on Tue Jun 14 2005 - 10:14:23 CDT