Re: substr, substrb and raw datatype

On Tue, 14 Jun 2005 17:14:23 +0200, Rafal Dabrowa interested us by writing:

> 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.
>

The Oracle documentation also says:

"
 SUBSTR calculates lengths using characters as defined by the input  character set. SUBSTRB uses bytes instead of characters. "

SUBSTRB does the substring at BYTE boundaries. SUBSTR does the operation at CHAR boundaries

Now, in the Concepts manual, it states

"
The RAW and LONG RAW datatypes are used for data that is not to be interpreted (not converted when moving data between different systems) by Oracle. These datatypes are intended for binary data or byte strings. "

If the data is not to be interpreted, then where is the character bundary for SUBSTR to use? Elsewhere in the Concepts manual we read

"
When Oracle automatically converts RAW or LONG RAW data to and from CHAR data, the binary data is represented in hexadecimal form with one hexadecimal character representing every four bits of RAW data. For example, one byte of RAW data with bits 11001011 is displayed and entered as 'CB.'
"

which implies the operation may be nibble-oriented. This seems consistent with your findings.

But, one may ask WHY you are doing character-based substrings of binary data? (Rhetorical question <g>)

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** I no longer assist with top-posted newsgroup queries ***