mysql复习第二天：单行函数

- 日理万妓 2023-08-17 16:03 137阅读 0赞

### 1、常用字符函数 ###

*  查看字节编码

SHOW VARIABLES LIKE '%char%';

![在这里插入图片描述][20190927205025330.png]

*  获取字符串的自己长度

SELECT LENGTH('我是卫卫) AS a,LENGTH('111') AS b;

![在这里插入图片描述][20190927205034230.png]

*  UPPER：将字符串转成大写，LOWER：将字符串转成小写

SELECT UPPER('aaa') AS a,LOWER('AAA') AS b;

![在这里插入图片描述][2019092720505283.png]

*  substr 截取从指定索引后的字符串(mysql是从1开始)

SELECT SUBSTR('123456789',2) AS a;

![在这里插入图片描述][20190927205115578.png]

*  substr 截取从指定索引处制定长度的字符串

SELECT SUBSTR('123456789',);

![在这里插入图片描述][20190927205149478.png]

*  instr返回子串第一次出现的问题,找不到返回0

SELECT SUBSTR('123456789',1,4) AS a;

![在这里插入图片描述][20190927205209728.png]

*  trim去除前后指定字符串,默认去除前后空格

SELECT LENGTH(TRIM('  12343  '));
    SELECT TRIM('a' FROM 'aaaaa12345aaaa') AS a;

![在这里插入图片描述][20190927205237870.png]

*  lpad用指定字符实现左填充制定长度

SELECT LPAD('1234',10,'*') AS a;

![在这里插入图片描述][20190927205246770.png]

*  rpad用指定字符实现右填充制定长度

SELECT RPAD('1234',10,'*') AS a;

![在这里插入图片描述][20190927205257670.png]

*  replace 替换

SELECT REPLACE('1213141','1','2') AS a;

![在这里插入图片描述][20190927205306252.png]

[20190927205025330.png]: /images/20230810/3585fe8d2955472d936da5952ac54446.png
[20190927205034230.png]: /images/20230810/e36623cc367c41d1a1abb7edd248d8ac.png
[2019092720505283.png]: /images/20230810/58936bec6f4d49f8b1c6b28de8046940.png
[20190927205115578.png]: /images/20230810/8665430fc6c14ae8a0ecde33b9e69efd.png
[20190927205149478.png]: /images/20230810/a444fa5498fd4b638f5b1d94fb23b4c2.png
[20190927205209728.png]: /images/20230810/74956f6e4384453b9c553ea97d720269.png
[20190927205237870.png]: /images/20230810/353fddaf1e2045a5a5920791f0e7cac6.png
[20190927205246770.png]: /images/20230810/8dd46a10f1694c7384c47dda584d8b90.png
[20190927205257670.png]: /images/20230810/79d5b26bcf4d4169acd136075f2e8b57.png
[20190927205306252.png]: /images/20230810/7f65bc148460475aa36759c8729501ba.png