1.连接字符串

SELECT
    CONCAT( 'Hello', ' ', 'world!' ) str01, # 依次连接字符串
    CONCAT_WS( ' ', 'Hello', 'my', 'world!' ) str02, # 以参数1为间隔，连接后面的每个字符串
    LPAD( '123', 6, '0' ) str03, # 在左边补足参数1
    LPAD( '1234567', 6, '0' ) str04, # 如果参数1超长，会截取
    RPAD( '123', 6, '0' ) str05, # 在右边补足参数1
    RPAD( '1234567', 6, '0' ) str06, # 如果参数1超长，会截取
    REPEAT( 'Hello ', 2 ) str07, # 参数1*参数2
    SPACE( 7 ) str08 # N个空格
    ;
/*
+--------------+-----------------+--------+--------+--------+--------+--------------+---------+
| str01        | str02           | str03  | str04  | str05  | str06  | str07        | str08   |
+--------------+-----------------+--------+--------+--------+--------+--------------+---------+
| Hello world! | Hello my world! | 000123 | 123456 | 123000 | 123456 | Hello Hello  |         |
+--------------+-----------------+--------+--------+--------+--------+--------------+---------+
1 row in set (0.002 sec)
*/




SELECT
  GROUP_CONCAT(str) str01, # 聚合拼接
  GROUP_CONCAT(str SEPARATOR ' ') str02, # 指定分隔符，默认为半角逗号
  GROUP_CONCAT(str ORDER BY id DESC SEPARATOR ' ') str03, # 指定顺序
  GROUP_CONCAT(DISTINCT str ORDER BY id DESC SEPARATOR ' ') str04 # 去除重复字符串
 FROM
 (
  SELECT 1 id, 'World!' str UNION ALL
  SELECT 2 id, 'World!' str UNION ALL
  SELECT 3 id, 'Hello' str
 ) t;
/*
+---------------------+---------------------+---------------------+--------------+
| str01               | str02               | str03               | str04        |
+---------------------+---------------------+---------------------+--------------+
| World!,World!,Hello | World! World! Hello | Hello World! World! | Hello World! |
+---------------------+---------------------+---------------------+--------------+
1 row in set (0.002 sec)
*/

2.截取字符串

SELECT
  LEFT('Hello world!', 5) str01, # 从左边截取n个长度的字符串
  RIGHT('Hello world!', 6) str02, # 从右边截取n个长度的字符串
  SUBSTR('Hello world!', 7) str03, # 从左边第n个字符开始截取字符串
  SUBSTRING('Hello world!', 7) str04,
  SUBSTR('Hello world!' FROM 7) str05,
  SUBSTRING('Hello world!' FROM 7) str06,
  SUBSTR('Hello world!', 7, 5) str07, # 从左边第n个字符开始截取m长度的字符串
  SUBSTRING('Hello world!', 7, 5) str08,
  SUBSTR('Hello world!' FROM 7 FOR 5) str09,
  SUBSTRING('Hello world!' FROM 7 FOR 5) str10
  ;
/*
+-------+--------+--------+--------+--------+--------+-------+-------+-------+-------+
| str01 | str02  | str03  | str04  | str05  | str06  | str07 | str08 | str09 | str10 |
+-------+--------+--------+--------+--------+--------+-------+-------+-------+-------+
| Hello | world! | world! | world! | world! | world! | world | world | world | world |
+-------+--------+--------+--------+--------+--------+-------+-------+-------+-------+
1 row in set (0.001 sec)
*/




SELECT
  SUBSTRING_INDEX('Hello my world!', ' ', 2) str11, #  以第二个参数为分隔符，分隔第一个参数中的字符串，并取出前n个字符串
  SUBSTRING_INDEX('Hello my world!', ' ', -2) str12, # 若n<0，则从右边开始计数
  SUBSTRING_INDEX('Hello my world!', 's', 1) str13, # 若参数2不存在于参数1中，则分隔操作不会进行
  SUBSTRING_INDEX('Hello my world!', '', 1) str14, # 若参数2=''，则分隔会得到一个空数组
  LTRIM('   Hello world!   ') str15, # 清除左空格
  RTRIM('   Hello world!   ') str16 # 清除右空格
  ;
/*
+----------+-----------+-----------------+-------+-----------------+-----------------+
| str11    | str12     | str13           | str14 | str15           | str16           |
+----------+-----------+-----------------+-------+-----------------+-----------------+
| Hello my | my world! | Hello my world! |       | Hello world!    |    Hello world! |
+----------+-----------+-----------------+-------+-----------------+-----------------+
1 row in set (0.002 sec)
*/

3.格式转换

SELECT
  LOWER('Hello world!') str01, # 转换成小写字母
  UPPER('Hello world!') str02, # 转换成大写字母
  REVERSE('Hello world!') str03, # 反转字符串
  FORMAT(123456.789, 2) str04, # 以 ###,###.##格式化数字，参数2是保留的小数位数，四舍五入
  FORMAT(123, 2) str05, # 小数位数不足时，补0
  CHAR(72, 101, 108, 108, 111, 32, 119, 111, 114, 108, 100, 33) str06, # 将十进制编码转换成字符串
  HEX('Hello world!') str07 # 取字符串的十六进制编码
 ;
/*
+--------------+--------------+--------------+------------+--------+--------------+--------------------------+
| str01        | str02        | str03        | str04      | str05  | str06        | str07                    |
+--------------+--------------+--------------+------------+--------+--------------+--------------------------+
| hello world! | HELLO WORLD! | !dlrow olleH | 123,456.79 | 123.00 | Hello world! | 48656C6C6F20776F726C6421 |
+--------------+--------------+--------------+------------+--------+--------------+--------------------------+
1 row in set (0.001 sec)
*/

4.字符串长度

SELECT
  LENGTH('Hello world!') str01, # 字节数量，UTF8编码下：英文、数字、半角符号=1个字节
  BIT_LENGTH('Hello world!') str02, # 字节位数
  CHAR_LENGTH('Hello world!') str03, # 字符数量
  CHARACTER_LENGTH('Hello world!') str04, # 与CHAR_LENGTH相同
  LENGTH('你好！') str05, # 字符长度，UTF8编码下：汉字、全角符号=3个字节
  BIT_LENGTH('你好！') str06,
  CHAR_LENGTH('你好！') str07,
  CHARACTER_LENGTH('你好！') str08
  ;
/*
+-------+-------+-------+-------+-------+-------+-------+-------+
| str01 | str02 | str03 | str04 | str05 | str06 | str07 | str08 |
+-------+-------+-------+-------+-------+-------+-------+-------+
|    12 |    96 |    12 |    12 |     6 |    48 |     3 |     3 |
+-------+-------+-------+-------+-------+-------+-------+-------+
1 row in set (0.016 sec)
*/

5.查找和比较字符串

SELECT
  INSTR('Hello world!', 'l') str01, # 返回参数2在参数1中第一次出现的位置
  INSTR('l', 'Hello world!') str02, # 没有出现过会返回0
  STRCMP('Hello', 'Hello') str03, # 比较两个字符串大小，相等返回0
  STRCMP('Hello1', 'Hello') str04, # 参数1>参数2，返回1
  STRCMP('Hello', 'Hello1') str05, # 参数1<参数2，返回-1
  FIND_IN_SET('Hello', 'Hello,world!') str06 # 以半角逗号分隔参数2，得到一个数组，并返回参数1在该数组中首次出现的位置
  ;
/*
+-------+-------+-------+-------+-------+-------+
| str01 | str02 | str03 | str04 | str05 | str06 |
+-------+-------+-------+-------+-------+-------+
|     3 |     0 |     0 |     1 |    -1 |     1 |
+-------+-------+-------+-------+-------+-------+
1 row in set (0.002 sec)
*/