MySQL: Select all fields start with given number and next character is letter?

Question

I have a table with random varchar number/letters as such:

1a
101a
101b
101c
11b
14a
14b
14c
14z
108a
108b

and I would like to SELECT * FROM TABLE WHERE VAR = [SPECIFIC NUMBER]/FOLLOWING ANY LETTER.

For example I am using this wrong method where it selects everything starting with 1:

SELECT * 
FROM  `table` 
WHERE var LIKE  '1%'

which gives me all of the above example because all start with 1. I want this to select only: 1a

Answer 1

Use REGEXP

SELECT * 
FROM  `table` 
WHERE var REGEXP '^1[[:alnum:]]'

Regexp explanation:

• ^ for start of string
• 1 for your specific number
• [[:alnum:]] for an alfanumeric character - if you strictly want letters, use alpha here. e.g.

SELECT * 
FROM  `table` 
WHERE var REGEXP '^1[[:alpha:]]'

Demo on SQL Fiddle