Validate pattern of field in combination of char & int

[syminder]

Is the SQL possible to validate a table field which is combination of char and int? e.g. MIS0014?

I want the SQL return data which specific field first 3 character is alphabets and last 4 character is numeric values.

[syminder]

Hi, I get the answer already.

I used the REGEXP_LIKE(fie ld, '^[a-z]{3}[0-9]{4}$') to solve my question, please ignore this thread.

[amitpatel66]

Prior 10g,

[code=oracle]
SQL> ed
Wrote file afiedt.buf

1 with t as (select 'MIS0014' col1 from dual
2 union select 'ABC1234' from dual
3 union select '1234ABC' from dual
4 union select 'CDV1234' FROM dual
5 union select 'CDVF123' FROM dual)
6 SELECT col1 FROM
7 (SELECT col1,translate( SUBSTR(col1,1,3 ),'4567890123', ' ') str1,
8 TRANSLATE(SUBST R(col1,4,4),'AB CDEFGHIJKLMNOPQ RSTUVWXYZ',' ') str2 FROM t)
9 WHERE length(str1) = 3
10* AND length(str2) = 4
SQL> /

COL1
-------
ABC1234
CDV1234
MIS0014

SQL>
[/code]