All posts by Galaxy Internet

Software Reviews

Navicat 12

Navicat has come out with a new major release, version 12. As an active user of Navicat 11, the first thing I noticed was the new Snippets feature. Navicat 12 comes with a few helpful snippets already built in, but the great thing is that you can create and save your own snippets. This feature alone will be a tremendous time saver for me as I often re-use several custom query's in my DB design process.

A simple Drag N Drop to the query window will reveal yet another great feature, text placeholders for adding your specific conditions. Version 12 now features a connection and database drop down in the query window that allows you to change the connection and DB to run the same query without leaving the query window.

A nice bonus is the snippet search that dynamically narrows the snippet list as you type your search. When you create a new Snippet you have the option of creating or selecting a Label for the snippet. What this does is basically groups your snippets so that when you select the snippet drop down filter it only shows snippets in that label group. The code completion in Navicat 12 has also been greatly improved

There are many, many improvements in this release that will improve productivity. Navicat 12 continues to be the leader in database management applications. Download a free trial!

Uncategorized

Mysql Natural Sort Mixed Alphanumeric – Numeric Alphanumeric First

SQL:

SELECT
stock
FROM
cards
ORDER BY
IF(stock REGEXP '^[A-Z]',
CONCAT(
LEFT(stock, 1),
LPAD(SUBSTRING(stock, 2), 20, '0')),
CONCAT(
'@',
LPAD(stock, 20, '0')))

SQL SCHEMA:

— —————————-
— Table structure for cards
— —————————-
DROP TABLE IF EXISTS `cards`;
CREATE TABLE `cards` (
`stock` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

— —————————-
— Records of cards
— —————————-
INSERT INTO `cards` VALUES ('I1');
INSERT INTO `cards` VALUES ('I2');
INSERT INTO `cards` VALUES ('I3');
INSERT INTO `cards` VALUES ('I4');
INSERT INTO `cards` VALUES ('W1');
INSERT INTO `cards` VALUES ('W2');
INSERT INTO `cards` VALUES ('W3');
INSERT INTO `cards` VALUES ('W10');
INSERT INTO `cards` VALUES ('W11');
INSERT INTO `cards` VALUES ('I12');
INSERT INTO `cards` VALUES ('I13');
INSERT INTO `cards` VALUES ('I14');
INSERT INTO `cards` VALUES ('I15');
INSERT INTO `cards` VALUES ('I20');
INSERT INTO `cards` VALUES ('IC1');
INSERT INTO `cards` VALUES ('IC2');
INSERT INTO `cards` VALUES ('IC3');

"This transforms a number like '123' to '@00000000000000000123' and an alphanumeric like 'A22' to 'A00000000000000000022'. These strings will sort in the fashion you want." — NovaDenizen

SOURCE: https://www.experts-exchange.com/questions/22092935/Ordering-mixed-numeric-alphanumeric-list.html

Modified for more varied Data

mysql> SELECT
data
FROM
mytable
ORDER BY
IF(data REGEXP '^[A-Z]',
CONCAT(
LEFT(data, 1),
LPAD(SUBSTRING(data, 3), 20, '0')),
CONCAT(
'@',
LPAD(data, 20, '0')
)),
LENGTH( data ),
data;
+———–+
| data |
+———–+
| AAAA1 |
| B2 |
| D&DC1 |
| D&DC2 |
| D&DC11 |
+———–+