摘要:
下文讲述MySQL数据库中系统函数SUBSTRING的功能说明,如下所示;
系统函数SUBSTRING的功能:
SUBSTRING函数的功能:
用于从字符串的指定位置开始截取指定长度的字符串
substring语法:
SUBSTRING(string, start, length)
或
SUBSTRING(string FROM start FOR length)
------参数说明-------
string:必须项。要从中提取的字符串
start:必须项。起始位置。可以是正数或负数。如果是正数,则此函数从字符串的开头提取。
如果是负数,则此函数从字符串的末尾提取
length:可选项。要提取的字节数。如果省略,将返回整个字符串(从起始位置)
注意事项:
1.length小于等于零时,则返回空字符串
2.start等于零时,则substring返回空字符串
3.start等于-1时,代表从倒数第一个字节位置开始截取
4.start等于-2时,代表从倒是第二个字节位置开始截取
依次类推
5.注意字节数的处理
SUBSTRING函数示例说明:
mysql> select substring('猫猫教程xfdmfxfdmf35.com',1,3);
+-------------------------------------------+
| substring('猫猫教程xfdmfxfdmf35.com',1,3) |
+-------------------------------------------+
| 猫 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select substring('xfdmfxfdmf35.com',1,100);
+---------------------------------+
| substring('xfdmfxfdmf35.com',1,100) |
+---------------------------------+
| xfdmfxfdmf35.com |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select substring('xfdmfxfdmf35.com',1,1);
+-------------------------------+
| substring('xfdmfxfdmf35.com',1,1) |
+-------------------------------+
| m |
+-------------------------------+
1 row in set (0.00 sec)
mysql> select substring('xfdmfxfdmf35.com',1,3);
+-------------------------------+
| substring('xfdmfxfdmf35.com',1,3) |
+-------------------------------+
| xfdmf |
+-------------------------------+
1 row in set (0.00 sec)
mysql> select substring('xfdmfxfdmf35.com',1,0);
+-------------------------------+
| substring('xfdmfxfdmf35.com',1,0) |
+-------------------------------+
| |
+-------------------------------+
1 row in set (0.00 sec)
mysql> select substring('xfdmfxfdmf35.com',1,-1);
+--------------------------------+
| substring('xfdmfxfdmf35.com',1,-1) |
+--------------------------------+
| |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select substring('xfdmfxfdmf35.com',1,-2);
+--------------------------------+
| substring('xfdmfxfdmf35.com',1,-2) |
+--------------------------------+
| |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select substring('xfdmfxfdmf35.com',0,-2);
+--------------------------------+
| substring('xfdmfxfdmf35.com',0,-2) |
+--------------------------------+
| |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select substring('xfdmfxfdmf35.com',0,1);
+-------------------------------+
| substring('xfdmfxfdmf35.com',0,1) |
+-------------------------------+
| |
+-------------------------------+
1 row in set (0.00 sec)
mysql> select substring('xfdmfxfdmf35.com',-1,1);
+--------------------------------+
| substring('xfdmfxfdmf35.com',-1,1) |
+--------------------------------+
| m |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select substring('xfdmfxfdmf35.com',-1,2);
+--------------------------------+
| substring('xfdmfxfdmf35.com',-1,2) |
+--------------------------------+
| m |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select substring('xfdmfxfdmf35.com',-1,3);
+--------------------------------+
| substring('xfdmfxfdmf35.com',-1,3) |
+--------------------------------+
| m |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select substring('xfdmfxfdmf35.com',-3,3);
+--------------------------------+
| substring('xfdmfxfdmf35.com',-3,3) |
+--------------------------------+
| com |
+--------------------------------+
1 row in set (0.00 sec)