CODEWITHSUNDEEP
oracle10gguid
acadia
acadia

Reputation: 2621

Validate GUID value in a Oracle Column

I am storing GUIDs in a column in oracle 10g table. The datatype of the column is Varchar(2). Now I want to check if the values in this column are valid GUIDs.

How can I query in Oracle to check if the values are valid GUIDs?

Upvotes: 3

Views: 2041

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

I've taken test samples from here and the regexp from here:

create table guids ( guid varchar(36) );
insert into guids values ( 'BC1EAAAF-1B5A-4695-9797-3ED6C99B7FC5' );
insert into guids values ( 'BCXXAAAF-1B5A-4695-9797-3EDXXXXXXFC5' );
insert into guids values ( '-jk1029347 lka llur 193241 lk;qed' );

select guid,
    case when regexp_like(guid,
'^({|\()?[A-Fa-f0-9]{8}-([A-Fa-f0-9]{4}-){3}[A-Fa-f0-9]{12}(}|\))?$'
    )
  then 'Y'
  else 'N'
  end AS is_valid
from guids;

GUID                                 IS_VALID 
------------------------------------ -------- 
BC1EAAAF-1B5A-4695-9797-3ED6C99B7FC5 Y        
BCXXAAAF-1B5A-4695-9797-3EDXXXXXXFC5 N        
-jk1029347 lka llur 193241 lk;qed    N

Upvotes: 3

eaolson
eaolson

Reputation: 15094

I assume you mean VARCHAR2. VARCHAR(2) would be a very short GUID.

I wouldn't store these values in the database, then verify they are correct. Verify they are correct, then store them. Use a check constraint or a trigger. That way, you know what is in your database is good data.

Upvotes: 0

Related Questions