SQL match on letter and number arrangement

Is it possible to write an SQL statement which finds all rows which match a specific arrangement of letters/numbers?

i.e. SELECT myCol FROM myTable WHERE myCol='<letter><letter>开发者_如何学JAVA<number>'

Should return 12X and 34Y but not 123X or 34YY

I am using MySQL.

---

I haven't got MySQL on this machine to test but I think

SELECT myCol 
FROM myTable 
WHERE myCol REGEXP '^[0-9][0-9][A-Z]$'

Should do it. ^ is an anchor for start of string, $ for end of string and the rest should be self explanatory.

Looks like you could also use REGEXP '^[[:digit:]][[:digit:]][[:alpha:]]$' but completely untested and just going off the docs.

---

Many database management systems support regular expressions. So, in PostgreSQL 9.x for example, you can do this . . .

create table mytable (
  mycol varchar(10) primary key
);

insert into mytable values 
('12X'),
('34Y'),
('123X'),
('34YY');

And then

select * 
from mytable
where mycol ~ ('^[0-9][0-9][A-Z]$');

---

And Oracle's version.. here it is:

SELECT myCol
  FROM myTable
 WHERE REGEXP_LIKE(myCol, '^\d{2}[A-Z]$')

---

For sql server, you can use with PATINDEX. For mysql, you have REGEXP.

EDIT : Damien_The_Unbeliever pointed out I was wrong and that PATINDEX does not support regular expressions. That's correct but after some googling I found that you can use regular expressions in sql server because it hosts CLR.