mysql>>子串的使用-如排序-截取>>substr

------+表结构
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+

#举个栗子1:假如你要查询first_name的结果都是abcdf这种形式的，要拿到b也就是按第2个字符串排序
select *
from employees
order by substr(first_name, 2，1)

substr函数从1开始算，并不是从0开始算。这里2,1意思是从第2位开始，截取1位

+结果集如下

-------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-------------+--------+------------+
| 10034 | 1962-12-29 | Bader | Swan | M | 1988-09-21 |
| 10016 | 1961-05-02 | Kazuhito | Cappelletti | M | 1995-01-27 |
| 10031 | 1959-01-27 | Karsten | Joslin | M | 1991-09-01 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
| 10012 | 1960-10-04 | Patricio | Bridgland | M | 1992-12-18 |
| 10018 | 1954-06-19 | Kazuhide | Peha | F | 1987-04-03 |
| 499999| 1958-05-01 | Sachin | Tsukuda | M | 1997-11-30 |
| 10020 | 1952-12-24 | Mayuko | Warwick | M | 1991-01-26 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10021 | 1960-02-20 | Ramzi | Erde | M | 1988-02-10 |
+--------+------------+------------+-------------+--------+------------+

#举个栗子2，查询结果中first_name列，只保留截取的前2位字符串。

select substr(first_name, 1, 2)
from employees
limit 10;

+结果如下

------------------------+
| substr(first_name,1,2) |
+------------------------+
| Ge |
| Be |
| Pa |
| Ch |
| Ky |
| An |
| Tz |
| Sa |
| Su |
| Du |
+------------------------+