How to Validate data is Hexadecimal or not in Oracle

Question

Hai all,

I have a table tbl_1 with fields fl_1 (varchar2 type), contain Hexadecimal format. Now i want validate (query) where is in Hexa format and where is not.

any idea in oracle script (SQL Syntax)?

CREATE TABLE TBL_1 (HEX VARCHAR2(20));
INSERT INTO TBL_1 VALUES('9851010A'); 
INSERT INTO TBL_1 VALUES('9851010B'); 
INSERT INTO TBL_1 VALUES('FIRDI2'); 
INSERT INTO TBL_1 VALUES('FIRDI'); 
commit;

should only value 'FIRDI' AND 'FIRDI2' are false.

thank you very much.

Answer 1

Just test, whether the field contains only hex characters:

SELECT *
  FROM tbl_1
 WHERE translate(upper(fld_1),
                 '0123456789ABCDEF',
                 NULL) IS NOT NULL;

This query returns rows with non hexadecimal values. You may or may not add trim() to the condition. It depends whether space padding invalidates data in your point of view or not.

Answer 2

You can use the to_number and to_char functions for hex translations.

SELECT TO_number('0A','XX') from dual
10

SELECT TO_CHAR(10,'XX') from dual
 A

If you have a string that does not represent a hex number you will get an invalid number exception.

SELECT TO_number('RR','XX') from dual

ORA-01722: invalid number