MySQL函数之字符串函数

﹏ヽ暗。殇╰゛Y 2023-09-27 13:19 138阅读 0赞

**目录**

1.concat：将两个或多个字符串组合成一个字符串

2.length和char\_length函数：以字节和字符获取字符串的长度

3.left：返回具有指定长度的字符串的左边部分

4.right：回具有指定长度的字符串的右边部分

5.replace：搜索并替换字符串中的子字符串。

6.substring：从具有特定长度的位置开始提取一个子字符串。

7.trim：从字符串中删除不需要的字符。

8.find\_in\_set:在逗号分隔的字符串列表中找到一个字符串

9.format:格式化具有特定区域设置的数字，舍入到小数位数。

--------------------

### 1.concat：将两个或多个字符串组合成一个字符串 ###

MySQL `CONCAT()`函数需要一个或多个字符串参数，并将它们连接成一个字符串。`CONCAT()`函数需要至少一个参数，否则会引起错误

连接之前将所有参数转换为字符串类型。如果任何参数为`NULL`，则`CONCAT()`函数返回`NULL`值。

如果想用连接符，需要在连接的项目之间拼接上连接符。

查询语句：select CONCAT(supplier_id,'-',supplier_name) from bms_bills_memo where id = 4
    结果：6-供应商4

`CONCAT_WS()`函数将两个或多个字符串值与预定义的分隔符相连接。

第一个参数是分隔符，后面是要拼接的参数

只有分隔符为null时才返回null，如果项目为null时，则不拼接

查询语句：
    select CONCAT_WS('-',supplier_id,supplier_name) from bms_bills_memo where id = 4
    
    select CONCAT_WS('-',null,supplier_name) from bms_bills_memo where id = 4
    
    select CONCAT_WS('-',null,null) from bms_bills_memo where id = 4
    结果：
    6-供应商4
    供应商4
    空

### 2.length和char\_length函数：以字节和字符获取字符串的长度 ###

length：获取以字节为单位的字符串长度

char\_length：获取以字符为单位的字符串的长度

字节(Byte)是计量单位，表示数据量多少，是计算机信息技术用于计量存储容量的一种计量单位，通常情况下一字节等于八位。

字符(Character)计算机中使用的字母、数字、字和符号，比如'A'、'B'、'$'、'&'等。

一般在英文状态下一个字母或字符占用一个字节，一个汉字用两个字节表示。

*  ASCII 码中，一个英文字母（不分大小写）为一个字节，一个中文汉字为两个字节。
 *  UTF-8 编码中，一个英文字为一个字节，一个中文为三个字节。
 *  Unicode 编码中，一个英文为一个字节，一个中文为两个字节。
 *  符号：英文标点为一个字节，中文标点为两个字节。例如：英文句号 . 占1个字节的大小，中文句号 。占2个字节的大小。
 *  UTF-16 编码中，一个英文字母字符或一个汉字字符存储都需要 2 个字节（Unicode 扩展区的一些汉字存储需要 4 个字节）。
 *  UTF-32 编码中，世界上任何字符的存储都需要 4 个字节。

查询语句：
    select LENGTH(supplier_name) from bms_bills_memo  where id = 4
    
    select CHAR_LENGTH(supplier_name) from bms_bills_memo  where id = 4
    结果：
    10
    4
    表中数据
    supplier_name：供应商4

结合使用

SELECT postid,
           title,
           IF(CHAR_LENGTH(excerpt) > 20,
              CONCAT(LEFT(excerpt,20), '...'),
              excerpt) summary
    FROM posts;

我们使用`IF`函数来检查摘录列的长度是否大于`20`，并使用CONCAT语句将`excerpt`列的值与省略号(`...`)连接起来，否则只是获取全部摘录(`excerpt`)内容

### 3.left：返回具有指定长度的字符串的左边部分 ###

查询语句：
    select LEFT(client_name,4) from bms_bills_memo where id = 4
    结果：
    青岛自贸
    表中数据
    client_name：青岛自贸新零售体验中心有限公司

`LEFT()`函数接受两个参数：

*  `str`是要提取子字符串的字符串。
 *  `length`是一个正整数，指定将从左边返回的字符数。

`LEFT()`函数返回`str`字符串中最左边的长度字符。如果`str`或`length`参数为`NULL`，则返回`NULL`值。

如果`length`为`0`或为负，则`LEFT`函数返回一个空字符串。如果`length`大于`str`字符串的长度，则`LEFT`函数返回整个`str`字符串。

### 4.right：回具有指定长度的字符串的右边部分 ###

查询语句：
    select RIGHT(client_name,4) from bms_bills_memo where id = 4
    结果：
    有限公司
    表中数据：
    client_name：青岛自贸新零售体验中心有限公司

### 5.replace：搜索并替换字符串中的子字符串。 ###

有三个参数，它将`string`中的`old_string`替换为`new_string`字符串

请注意，当搜索要替换的文本时，MySQL使用区分大小写匹配来执行要替换的字符串的搜索

不支持[正则表达式][Link 1]

查询语句：
    select REPLACE(client_name,'青岛','威海') from bms_bills_memo where id = 4
    结果：
    威海自贸新零售体验中心有限公司
    
    查询语句：
    select REPLACE(bill_of_lading_no,'td','ab') from bms_bills_memo where id = 4
    结果：
    TD10004
    
    结果没有发生变化，因为区分大小写

### 6.substring：从具有特定长度的位置开始提取一个子字符串。 ###

SUBSTRING(string,position);
    SUBSTRING(string FROM position);

有两个参数：

*  `string`参数是要提取子字符串的字符串。
 *  `position`参数是一个[整数][Link 2]，用于指定子串的起始字符，`position`可以是正或负整数。

如果`position`为正，则`SUBSTRING`函数从字符串的开始处提取子字符串。

如果`position`参数为零，则`SUBSTRING`函数返回一个空字符串。

可以使用SQL标准语法与`FROM`关键字一起调用`SUBSTRING`函数

查询语句:
    select SUBSTRING(bill_of_lading_no,2) from bms_bills_memo where id = 4
    结果：
    D10004
    
    查询语句：
    select SUBSTRING(bill_of_lading_no,-2) from bms_bills_memo where id = 4
    结果：
    04
    
    表中数据：
    bill_of_lading_no:TD10004

如果要指定要从字符串中提取的子字符串的长度，可以使用以下形式的`SUBSTRING`函数

SUBSTRING(string,position,length);
    SUBSTRING(string FROM position FOR length);

查询语句：
    select SUBSTRING(bill_of_lading_no,2,5) from bms_bills_memo where id = 4
    结果：
    D1000
    
    查询语句：
    select SUBSTRING(bill_of_lading_no,-7,5) from bms_bills_memo where id = 4
    结果：
    TD100
    
    表中数据：bill_of_lading_no:TD10004

`SUBSTR()`函数是`SUBSTRING()`函数的同义词，所以可以互换使用它们。

SUBSTRING\_INDEX的用法

按照某个特定符号进行分割，并取出对应的值

查询语句：
    select SUBSTRING_INDEX('qwe-etr-tyu','-',1)
    select SUBSTRING_INDEX('qwe-etr-tyu','-',2)
    select SUBSTRING_INDEX('qwe-etr-tyu','-',-1)
    select SUBSTRING_INDEX('qwe-etr-tyu','-',-2)
    
    结果：
    qwe
    qwe-etr
    tyu
    etr-tyu

### 7.trim：从字符串中删除不需要的字符。 ###

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

您可以使用`LEADING`，`TRAILING`或`BOTH`选项明确指示`TRIM()`函数从字符串中删除前导，尾随或前导和尾随的不必要的字符。

如果您没有指定任何内容，`TRIM()`函数默认使用`BOTH`选项。

`[removed_str]`是要删除的字符串。默认情况下，它是一个空格。这意味着如果不指定特定的字符串，则`TRIM()`函数仅删除空格。

`str`是要删除子字符`removed_str`的字符串。

`TRIM()`函数返回一个字符串，删除不需要的字符。

查询语句：
    //从字符串中除去前导和尾随空格
    SELECT TRIM(' MySQL TRIM Function ');
    //仅删除前导空格
    SELECT TRIM(LEADING FROM '    MySQL TRIM Function   ');
    //仅删除尾随空格
    SELECT TRIM(TRAILING FROM '    MySQL TRIM Function   ');
    //删除字符串末尾的换行符
    -- 方式一
    SELECT 
        TRIM(TRAILING '\n' FROM field_name)
    FROM table_name;
    
    -- 方式二
    SELECT 
        TRIM(TRAILING '\r' FROM field_name)
    FROM table_name;
    
    -- 方式三
    SELECT 
        TRIM(TRAILING '\r\n' FROM field_name)
    FROM table_name;

如果要仅删除前导或尾随空格，则可以使用其他字符串函数：`LTRIM`和`RTRIM`

使用`LTRIM`函数来删除字符串的前导空格

SELECT LTRIM(' MySQL LTRIM function');  
使用`RTRIM()`函数来删除字符串的尾随空格

SELECT RTRIM('MySQL RTRIM function ');

### 8.find\_in\_set:在逗号分隔的字符串列表中找到一个字符串 ###

FIND_IN_SET(needle,haystack);

`FIND_IN_SET()`函数接受两个参数：

*  第一个参数`needle`是要查找的字符串。
 *  第二个参数`haystack`是要搜索的逗号分隔的字符串列表。

`FIND_IN_SET()`函数根据参数的值返回一个整数或一个`NULL`值：

*  如果`needle`或`haystack`为`NULL`，则函数返回`NULL`值。
 *  如果`needle`不在`haystack`中，或者`haystack`是空字符串，则返回零。
 *  如果`needle`在`haystack`中，则返回一个正整数。

请注意，如果`needle`包含逗号(`，`)，该函数将无法正常工作。 此外，如果`needle`是一个常量字符串，而且`haystack`是一个类型为`SET`的列，MySQL将使用位算术优化。

查询语句：
    select FIND_IN_SET('red','yellow,red,blue')
    结果：
    2

where中可以直接使用find\_in\_set

SELECT 
        name, belts
    FROM
        divisions
    WHERE
        FIND_IN_SET('red', belts);
    
    SELECT 
        name, belts
    FROM
        divisions
    WHERE
        NOT FIND_IN_SET('black', belts);

FIND\_IN\_SET函数与IN运算符具有相同的功能

`column IN (x, y, z)`表达式与`FIND_IN_SET(column, 'x,y,z')`相同。

`IN`运算符可以接受任意数量的参数，每个参数用逗号分隔。 但是，`FIND_IN_SET`函数只有两个参数。

当您希望将值与数据库中的值列表进行匹配时，可以使用`IN`运算符。 并且当希望将值与数据库中以逗号分隔存储的值列表进行匹配时，可以使用`FIND_IN_SET`函数。

### 9.format:格式化具有特定区域设置的数字，舍入到小数位数。 ###

FOMRAT(N,D,locale);

`FORMAT`函数将数字N格式化为格式，如`"#,###,###.##"`，舍入到`D`位小数。它返回一个值作为字符串。

`FORMAT`函数接受三个参数：

*  `N`是要格式化的数字。
 *  `D`是要四舍五入的小数位数。
 *  `locale`是一个可选参数，用于确定千个分隔符和分隔符之间的分组。如果省略`locale`操作符，MySQL将默认使用`en_US`。以下链接提供MySQL支持的所有区域名称：[http://dev.mysql.com/doc/refman/5.7/en/locale-support.html][http_dev.mysql.com_doc_refman_5.7_en_locale-support.html]

查询语句：
    select format(123456.1258,2)
    select format(123456.1248,2)
    结果：
    123,456.13
    123,456.12

可以组合两个函数：`FORMAT`和`CONCAT`。 `FORMAT`函数将库存值格式舍入为`2`位小数。 并且concat函数在库存值字符串的开头添加人民币符号(`￥`)

SELECT 
        productname,
        CONCAT('￥',
                FORMAT(quantityInStock * buyPrice, 2)) stock_value
    FROM
        products;

[Link 1]: http://www.yiibai.com/mysql/regular-expression-regexp.html
[Link 2]: http://www.yiibai.com/mysql/int.html
[http_dev.mysql.com_doc_refman_5.7_en_locale-support.html]: http://dev.mysql.com/doc/refman/5.7/en/locale-support.html