CODEWITHSUNDEEP
sqloracle-database
Firdi
Firdi

Reputation: 141

How to Validate data is Hexadecimal or not in Oracle

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.

Upvotes: 1

Views: 5665

Answers (3)

Aaron
Aaron

Reputation: 214

I was unable to work the above translate example into a reliable solution.

I instead created a solution using REGEXP_INSTR.

SELECT * FROM tbl_1 WHERE REGEXP_INSTR( UPPER(fld_1), '[^0123456789ABCDEF]' ) > 0;

The above will return rows that include non hex characters.

I also turned this into a is_hex function that either returns 'T' or 'F':

create or replace FUNCTION "IS_HEX" (i_value in VARCHAR2)
RETURN CHAR
IS
    v_result CHAR(1) := 'F';
BEGIN

    IF REGEXP_INSTR( UPPER(i_value), '[^0123456789ABCDEF]') = 0 THEN
        v_result := 'T';
    END IF;

    return v_result;
END;

Allowing you to do the following:

SELECT * FROM tbl_1 WHERE is_hex(fld_1) = 'F';

Upvotes: 1

Rene
Rene

Reputation: 10541

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

Upvotes: 0

Michal Pravda
Michal Pravda

Reputation: 829

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.

Upvotes: 3

Related Questions