CODEWITHSUNDEEP
phpmysqlsql
jQuerybeast
jQuerybeast

Reputation: 14490

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

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

Upvotes: 4

Views: 239

Answers (1)

Konerak
Konerak

Reputation: 39763

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

Upvotes: 10

Related Questions