Oracle raw datatype in that place

Question

I have a column of type RAW in my database. How can I use it in the where section?
to get only values ​​with a third byte equal to 4.
this does not work:

SELECT v from T where v[3]=4 

Answer 1

use the UTL_RAW functions to interact with raw files, for example:

 SQL> create table test (a raw(16)); Table created SQL> insert into test values ('FF00FF00FF'); 1 row inserted SQL> select * from test where utl_raw.substr(a, 3, 1) = 'FF'; A -------------------------------- FF00FF00FF 

Answer 2

You can also use the REGEXP_LIKE function to select rows with a RAW data type:

 select * from test where REGEXP_LIKE(a,'^....04.*')"; 

In my case, using this method is slightly faster than utl_raw.substr.

Answer 3

in Oracle : you can use the HEXTORAW function

• select * from TBLTest01, where (BINCOL = hextoraw ('f0f0f3'))

it is different in other databases. for example in DB2 :

• select * from TBLTest01, where BINCOL = BINARY (x'F0F0F3 ')