mysql 行列内置函数_MySQL 内置函数

最新推荐文章于 2023-08-23 18:19:34 发布

思睿-three

最新推荐文章于 2023-08-23 18:19:34 发布

阅读量97

点赞数

文章标签： mysql 行列内置函数

本文链接：https://blog.csdn.net/weixin_33983238/article/details/113938085

版权

CHAR_LENGTH(str)

返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。

+------------------------+

| CHAR_LENGTH('klvchen') |

+------------------------+

| 7 |

+------------------------+

1 row in set (0.00 sec)

mysql> SELECT CHAR_LENGTH('你');

+--------------------+

| CHAR_LENGTH('你') |

+--------------------+

| 1 |

+--------------------+

1 row in set (0.00 sec)

CONCAT(str1,str2,...)

字符串拼接, 如有任何一个参数为NULL ，则返回值为 NULL。

mysql> SELECT CONCAT('kl','v','chen');

+-------------------------+

| CONCAT('kl','v','chen') |

+-------------------------+

| klvchen |

+-------------------------+

1 row in set (0.00 sec)

CONCAT_WS(separator,str1,str2,...)

字符串拼接(自定义连接符)CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)

mysql> SELECT CONCAT_WS('_','chen','wj');

+----------------------------+

| CONCAT_WS('_','chen','wj') |

+----------------------------+

| chen_wj |

+----------------------------+

1 row in set (0.01 sec)

mysql> SELECT CONCAT_WS('_','chen',' wj ', NULL, 'hehe');

+--------------------------------------------+

| CONCAT_WS('_','chen',' wj ', NULL, 'hehe') |

+--------------------------------------------+

| chen_ wj _hehe |

+--------------------------------------------+

1 row in set (0.00 sec)

CONV(N,from_base,to_base)

进制转换

mysql> SELECT CONV('a',16,2);

+----------------+

| CONV('a',16,2) |

+----------------+

| 1010 |

+----------------+

1 row in set (0.00 sec)

FORMAT(X,D)

将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位，并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。

mysql> SELECT FORMAT(1.23,4);

+----------------+

| FORMAT(1.23,4) |

+----------------+

| 1.2300 |

+----------------+

1 row in set (0.00 sec)

INSERT(str,pos,len,newstr)

str 原字符串; pos 要替换位置其实位置(从1开始); len：替换的长度；newstr：需要替换的符串

简洁概况为：先从 pos 处删除 len 长度的字符串，再由新的字符串代替

mysql> SELECT INSERT('klvchen', 1, 1, 'j');

+------------------------------+

| INSERT('klvchen', 1, 1, 'j') |

+------------------------------+

| jlvchen |

+------------------------------+

1 row in set (0.00 sec)

mysql> SELECT INSERT('klvchen', 1, 1, 'ja');

+-------------------------------+

| INSERT('klvchen', 1, 1, 'ja') |

+-------------------------------+

| jalvchen |

+-------------------------------+

1 row in set (0.00 sec)

mysql> SELECT INSERT('klvchen', 1, 2, 'a');

+------------------------------+

| INSERT('klvchen', 1, 2, 'a') |

+------------------------------+

| avchen |

+------------------------------+

1 row in set (0.00 sec)

INSTR(str,substr)

返回字符串 str 中子字符串的第一个出现位置。

mysql> SELECT INSTR('klvchen','l');

+----------------------+

| INSTR('klvchen','l') |

+----------------------+

| 2 |

+----------------------+

1 row in set (0.00 sec)

LOCATE(substr,str,pos)

获取子序列索引位置

mysql> SELECT LOCATE('l','klvchen',1);

+-------------------------+

| LOCATE('l','klvchen',1) |

+-------------------------+

| 2 |

+-------------------------+

1 row in set (0.01 sec)

LEFT(str,len)

返回字符串 str 从开始的 len 位置的子序列字符。

mysql> SELECT LEFT('klvchen',2);

+-------------------+

| LEFT('klvchen',2) |

+-------------------+

| kl |

+-------------------+

1 row in set (0.00 sec)

RIGHT(str,len)

从字符串 str 开始，返回从后边开始 len个字符组成的子序列

mysql> SELECT RIGHT('klvchen', 2);

+---------------------+

| RIGHT('klvchen', 2) |

+---------------------+

| en |

+---------------------+

1 row in set (0.00 sec)

LOWER(str)

字符串转化为小写

mysql> SELECT LOWER('KLVCHEN');

+------------------+

| LOWER('KLVCHEN') |

+------------------+

| klvchen |

+------------------+

1 row in set (0.00 sec)

UPPER(str)

字符串转化为大写

mysql> SELECT UPPER('klvchen');

+------------------+

| UPPER('klvchen') |

+------------------+

| KLVCHEN |

+------------------+

1 row in set (0.00 sec)

LTRIM(str)

返回字符串 str ，左边的空格字符被删除。

mysql> SELECT LTRIM(' klvchen');

+--------------------+

| LTRIM(' klvchen') |

+--------------------+

| klvchen |

+--------------------+

1 row in set (0.00 sec)

RTRIM(str)

返回字符串 str ，结尾空格字符被被删除。

mysql> SELECT RTRIM('klvchen ');

+----------------------+

| RTRIM('klvchen ') |

+----------------------+

| klvchen |

+----------------------+

1 row in set (0.00 sec)

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)

返回字符串 str ，其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。

mysql> SELECT TRIM(' kl ');

+------------------+

| TRIM(' kl ') |

+------------------+

| kl |

+------------------+

1 row in set (0.00 sec)

mysql> SELECT TRIM(LEADING 'x' FROM 'xxklxx');

+----------------------------------+

| TRIM(LEADING 'x' FROM 'xxklxx') |

+----------------------------------+

| klxx |

+----------------------------------+

1 row in set (0.00 sec)

mysql> SELECT TRIM(BOTH 'x' FROM 'xxklxx');

+-------------------------------+

| TRIM(BOTH 'x' FROM 'xxklxx') |

+-------------------------------+

| kl |

+-------------------------------+

1 row in set (0.00 sec)

mysql> SELECT TRIM(trailing 'x' FROM 'xxklxx');

+-----------------------------------+

| TRIM(trailing 'x' FROM 'xxklxx') |

+-----------------------------------+

| xxkl |

+-----------------------------------+

1 row in set (0.00 sec)

REPEAT(str,count)

返回一个由重复的字符串 str 组成的字符串，字符串str重复的次数等于count的值。

若 count <= 0,则返回一个空字符串；若str 或 count 为 NULL，则返回 NULL 。

mysql> SELECT REPEAT('ha',3);

+----------------+

| REPEAT('ha',3) |

+----------------+

| hahaha |

+----------------+

1 row in set (0.00 sec)

REPLACE(str,from_str,to_str)

返回字符串str 以及所有被字符串to_str替代的字符串from_str 。

mysql> SELECT REPLACE('klvchel', 'l', 'i');

+------------------------------+

| REPLACE('klvchel', 'l', 'i') |

+------------------------------+

| kivchei |

+------------------------------+

1 row in set (0.00 sec)

REVERSE(str)

返回字符串 str ，顺序和字符顺序相反。

mysql> SELECT REVERSE('klvchen');

+--------------------+

| REVERSE('klvchen') |

+--------------------+

| nehcvlk |

+--------------------+

1 row in set (0.00 sec)

SUBSTRING(str,pos,len)

获取字符串子序列

mysql> SELECT SUBSTRING('klvchen', 1, 3);

+----------------------------+

| SUBSTRING('klvchen', 1, 3) |

+----------------------------+

| klv |

+----------------------------+

1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('klvchen', 3);

+-------------------------+

| SUBSTRING('klvchen', 3) |

+-------------------------+

| vchen |

+-------------------------+

1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('klvchen' FROM 3);

+-----------------------------+

| SUBSTRING('klvchen' FROM 3) |

+-----------------------------+

| vchen |

+-----------------------------+

1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('klvchen', -3);

+--------------------------+

| SUBSTRING('klvchen', -3) |

+--------------------------+

| hen |

+--------------------------+

1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('klvchen', -3, 2);

+-----------------------------+

| SUBSTRING('klvchen', -3, 2) |

+-----------------------------+

| he |

+-----------------------------+

1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('klvchen' FROM -3 FOR 2);

+------------------------------------+

| SUBSTRING('klvchen' FROM -3 FOR 2) |

+------------------------------------+

| he |

+------------------------------------+

1 row in set (0.01 sec)

内容来源于网络如有侵权请私信删除

思睿-three

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 行列内置函数_MySQL 内置函数

CHAR_LENGTH(str)返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。+------------------------+| CHAR_LENGTH('klvchen') |+------------------------+| 7 |+------------------------+1 row in set (0...
复制链接

扫一扫