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

mysql, php, sql

Solution

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

Problem

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`

Original source