- Left(str,len):从左往右,截取str中的len个字符:
mysql> select left('abcde',2);
+-----------------+
| left('abcde',2) |
+-----------------+
| ab |
+-----------------+
1 row in set
- Right(str,len):从右往左,截取str中的len个字符:
mysql> select right('abcde',2);
+------------------+
| right('abcde',2) |
+------------------+
| de |
+------------------+
1 row in set
- Substring(str,pos,len):在第pos个字符开始(包括该位置),从左往右(无论pos是正是负)截取str的len个字符:
mysql> select substr('abcdef',2,2);
+----------------------+
| substr('abcdef',2,2) |
+----------------------+
| bc |
+----------------------+
1 row in set
mysql> select substring('abcdef',-2,2);
+--------------------------+
| substring('abcdef',-2,2) |
+--------------------------+
| ef |
+--------------------------+
1 row in set
- Substring_index(str,str1,N):截取str中第| N |个str1到起始位置(由N的正负决定)所有字符,若未找到字符串str1,则返回整个字符串str:
mysql> select substring_index('abcdefcd','cd',2);
+------------------------------------+
| substring_index('abcdefcd','cd',2) |
+------------------------------------+
| abcdef |
+------------------------------------+
1 row in set
mysql> select substring_index('abcdefcd','cd',-2);
+-------------------------------------+
| substring_index('abcdefcd','cd',-2) |
+-------------------------------------+
| efcd |
+-------------------------------------+
1 row in set
mysql> select substring_index('abcdefcd','hh',-2);
+-------------------------------------+
| substring_index('abcdefcd','hh',-2) |
+-------------------------------------+
| abcdefcd |
+-------------------------------------+
1 row in set
主要注意区分substring和substring_index截取选取的方向。