Re: substr, substrb and raw datatype

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 14 Jun 2005 21:58:45 -0700
Message-ID: <1118811536.753204@yasure>

Rafal Dabrowa wrote:
> DA Morgan wrote:
>

>> SQL> CREATE TABLE t (
>>   2  charcol  VARCHAR2(20),
>>   3  rawcol   RAW(20));
>>
>> Table created.
>>
>> SQL> INSERT INTO t VALUES ('ABC', 'ABC');
>>
>> 1 row created.
>>
>> SQL> COMMIT;
>>
>> Commit complete.
>>
>> SQL> SELECT DUMP(charcol), DUMP(rawcol)
>>   2  FROM t;
>>
>> DUMP(CHARCOL)           DUMP(RAWCOL)
>> ----------------------  ---------------------
>> Typ=1 Len=3: 65,66,67   Typ=23 Len=2: 10,188

>
>
> But I have asked for differences between
> substr and substrb function on raw datatype.
> Why these functions give different results on
> the same data:
>
> sql> select substr(val, 5,2), substrb(val, 5, 2) from xyz;
>
> SU SUBS
> -- ----
> 03 0506
>
> Although on table with varchar2 datatype they give the same
> result:
>
> sql> desc zyx
> Name Null? Type
> ------------ -------- --------------
> ID NUMBER
> VAL VARCHAR2(20)
>
> sql> select substr(val, 5,2), substrb(val, 5, 2) from zyx;
>
> SU SU
> -- --
> 03 03

The point I was trying to make, perhaps too subtly, was that you should not be using RAW for character data. Hans stated exactly that with far more clarity.

In my example above the string 'ABC' is stored as 65,66,67 so substringing might return 65,66.

What do you expect to get, even if SUBSTR did work, with 10,188? the 10 or the 188? Neither is a letter.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Tue Jun 14 2005 - 23:58:45 CDT