CODEWITHSUNDEEP
sqloracle-database
Roman
Roman

Reputation: 1019

oracle raw datatype in where clause

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

SELECT v from T where v[3]=4

Upvotes: 9

Views: 18064

Answers (3)

afshar
afshar

Reputation: 703

in Oracle : you can use HEXTORAW function

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

it is in other databases in another way. for example in DB2 :

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

Upvotes: 3

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

use the functions of the UTL_RAW package to interact with raws, 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

Upvotes: 13

Roman
Roman

Reputation: 1019

One can also use REGEXP_LIKE function to select rows with RAW datatype:

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

In my use case this method is a little faster than utl_raw.substr.

Upvotes: 0

Related Questions