Content Entry

2.3 按子串排序

Published: 2008-11-18 Categories: Cookbook Tags: SQL DB2 MySQL Oracle PostgreSQL Database MSSQL substr substring

2.3 按子串排序
Q: 按照字符串某一部分排序, 如按照某字段值的最后两个字符排序
A: 使用 DBMS 内置函数

DB2, MySQL, Oracle, PostgreSQL ( substr() )
SELECT ename, job
FROM emp
ORDER BY substr(job, length(job) - 2)

+--------+-----------+
| ename | job |
+--------+-----------+
| KING | PRESIDENT |
| SMITH | CLERK |
| JAMES | CLERK |
| ADAMS | CLERK |
| MILLER | CLERK |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| WARD | SALESMAN |
| ALLEN | SALESMAN |
| FORD | ANALYST |
| SCOTT | ANALYST |
+--------+-----------+
14 rows in set (0.05 sec)

SQL Server ( substring() )
SELECT ename, job
FROM emp
ORDER BY substring(job, len(job) - 2, 2)

Tables Used:
for MySQL
http://www.hooto.com/home/rui/doc/archives/5089.html
for PostgreSQL
http://www.hooto.com/home/rui/doc/archives/5090.html

Learn:
SQL Cookbook, by Anthony Molinaro.
Copyright 2006 O'Reilly Media, Inc.

--EOF--

comments loading