mysql search in the nth digit of a number

This sounds like a near impossible-to-maintain idea. Yes, it's compact, but it's incomprehensible to a human, which makes it hard to work with. It's also nowhere near self-documenting; if you forget or mix up which number represents what you'll get into a whole load of problems and logic bugs.

You should be storing each separate kind of status in a separate column, and preferably use something as self-documenting as ENUMs as values:

CREATE TABLE foo (    payment_status      ENUM('paid', 'pending', 'rejected'),    registration_status ENUM('active', 'pending', 'deleted'),    ...)

Clean, simple, comprehensible, usable, maintainable, extendable; and probably a number of other *able more. I don't see any real advantage in using something else.

CREATE TABLE IF NOT EXISTS `test` (  `id` int(10) unsigned NOT NULL auto_increment,  `user_id` int(10) unsigned NOT NULL,  `status` int(9) unsigned zerofill NOT NULL,  PRIMARY KEY  (`id`),  KEY `status` (`status`))

to use SUBSTR

SELECT * FROM `test` WHERE SUBSTR( STATUS , 7, 1 ) = '2'

to use REGXP

SELECT * FROM `test` WHERE STATUS REGEXP '[0-9]{6}2'

Remove the last two digits with /100 and take only the last one with the modulo 10 operator

select (status / 100) % 10from your_table