The OOTB capability to describe the format of a text field is very limited and only handles very simple cases. Much more complex formats can be checked with sql queries. We use the following in a rule to validate a value and place an error message into a read-only text field. This particular rule is used in a table component so we do not have the option to clear the bad value. That is an option available on a request field. The field is defined as Text - 4. Regular expressions can define almost any rules you can imagine.
select 'x', decode(regexp_instr('[TE.VP.TRANSACTION_ID]',chr(91)||'^A-Z0-9$@#./-_%&?!:|=,;<>'||chr(93)),0,'','<!--HTML--> <b><font color="#B22222">Transaction ID contains invalid characters') from dual
Looks like your below query simply checks for unlimited A-Z characters in any number . This sure gets you what custom validation format LLDDD couldn't. But would you have any suggestion on how I can actually validate for only 'ABxxx' and 'DExxx' ? The catch in my case is that I can only have the characters literally starting with either 'AB' or 'DE' and then followed by 3 single digit numbers.
In my case:
Valid: AB345, DE876
Invalid: ABD345, AB34
I actually tried last week to go sql valdiation this way.. just wasn't able to come up with correct query :-( ..