MySQL Select when specific digit is x

Question

I have a column that contains values like 5A898, 89KAS, 89ASD.

I'm trying to write a query that will only return rows where the third digit of the column is 'A'. For instance '89ASD' would be returned but '89KAS' would not. I'm not sure what the right way to go about this is. Regular expressions?

So...

SELECT column
FROM table
WHERE column = ?

Marc B · Accepted Answer · 2012-10-19 15:45:02Z

2

WHERE column LIKE '__A%'
                   ^^-- 2 underscores

should do the trick. two "whatever" characters, followed by an A, then followed by anything in any amount.

answered Oct 19, 2012 at 15:45

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

eggyal Over a year ago

How efficient would this be versus SUBSTRING(column, 3, 1) = 'A' ?

Marc B Over a year ago

about the same. since you're using wildcards and derived values, neither will allow indexes to be used.

eggyal Over a year ago

Agreed, but surely pattern matching carries some significant overhead versus directly accessing the third character of a string?

Marc B Over a year ago

depends on how smart the query optimizer is. __ is fairly easy to handle. doing %A% would be a bit heavier.

John Woo · Accepted Answer · 2012-10-19 15:44:31Z

0

Maybe you can use MySQL's SUBSTRING

SELECT column
FROM table
WHERE SUBSTRING(column,3,1) = 'A'

answered Oct 19, 2012 at 15:44

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Barranka · Accepted Answer · 2012-10-19 15:46:08Z

0

where right(left(col,3),1) = 'A'

That can help... MarcB's answer is cleaner

answered Oct 19, 2012 at 15:46

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

Comments

Bjoern · Accepted Answer · 2012-10-19 15:47:19Z

0

You can do this with a regex, but I think it might be easier just coping with a string operation here:

SELECT column
FROM   table
WHERE  SUBSTRING(column,3,1) = 'A';

answered Oct 19, 2012 at 15:47

Bjoern

16.3k4 gold badges47 silver badges50 bronze badges

Collectives™ on Stack Overflow

MySQL Select when specific digit is x

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related