Mysql order varchar column like Excel


CREATE TABLE IF NOT EXISTS `varchar_order_by` (
  `text` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`text`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `varchar_order_by` (`text`) VALUES
('1'),
('10'),
('100'),
('101'),
('11'),
('12'),
('15'),
('1abc'),
('2'),
('23abcd'),
('2abcd'),
('3'),
('4'),
('8'),
('80'),
('85'),
('99'),
('abcd'),
('bcd'),
('dfgh');

Mysql Query

select text regexp '[a-zA-Z\s]' ,
text + 0 ,
text regexp '[a-zA-Z\s]' AND text + 0,
text from varchar_order_by
order by
text regexp '[a-zA-Z\s]' asc,
text regexp '[a-zA-Z\s]' AND text + 0 desc,
text + 0 asc,
text asc
Advertisements

Author: yogs174

I am programmer and entrepreneur and love innovative stuff. I like to work on different technologies with new tools.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s