Mysql字符串函数

我就是我 2021-07-25 02:20 669阅读 0赞

**一 使用CHAR\_LENGTH函数计算字符串字符个数**

mysql> SELECT CHAR_LENGTH('date'), CHAR_LENGTH('egg');
    +---------------------+--------------------+
    | CHAR_LENGTH('date') | CHAR_LENGTH('egg') |
    +---------------------+--------------------+
    |                   4 |                  3 |
    +---------------------+--------------------+
    1 row in set (0.00 sec)

**二 使用LENGTH函数计算字符串长度**

mysql> SELECT LENGTH('date'), LENGTH('egg');
    +----------------+---------------+
    | LENGTH('date') | LENGTH('egg') |
    +----------------+---------------+
    |              4 |             3 |
    +----------------+---------------+
    1 row in set (0.00 sec)

**三 使用CONCAT函数连接字符串**

mysql> SELECT CONCAT('My SQL', '5.6'),CONCAT('My',NULL, 'SQL');
    +-------------------------+--------------------------+
    | CONCAT('My SQL', '5.6') | CONCAT('My',NULL, 'SQL') |
    +-------------------------+--------------------------+
    | My SQL5.6               | NULL                     |
    +-------------------------+--------------------------+
    1 row in set (0.00 sec)

**四 使用CONCAT\_WS函数连接带分隔符的字符串**

mysql> SELECT CONCAT_WS('-', '1st','2nd', '3rd'), CONCAT_WS('*', '1st', NULL, '3rd');
    +------------------------------------+------------------------------------+
    | CONCAT_WS('-', '1st','2nd', '3rd') | CONCAT_WS('*', '1st', NULL, '3rd') |
    +------------------------------------+------------------------------------+
    | 1st-2nd-3rd                        | 1st*3rd                            |
    +------------------------------------+------------------------------------+
    1 row in set (0.00 sec)

**五 使用INSERT函数进行字符串替代操作**

SELECT INSERT('Quest', 2, 4, 'What') AS col1,
    INSERT('Quest', -1, 4, 'What') AS col2,
    INSERT('Quest', 3, 100, 'Wh') AS col3;
    +-------+-------+------+
    | col1  | col2  | col3 |
    +-------+-------+------+
    | QWhat | Quest | QuWh |
    +-------+-------+------+
    1 row in set (0.01 sec)

**六 使用LOWER函数或者LCASE函数将字符串中所有字母字符转换为小写**

**七 使用UPPER函数或者UCASE函数将字符串中所有字母字符转换为大写**

**八 使用LEFT函数返回字符串中左边的字符**

mysql> SELECT LEFT('football', 5);
    +---------------------+
    | LEFT('football', 5) |
    +---------------------+
    | footb               |
    +---------------------+
    1 row in set (0.00 sec)

**九 使用RIGHT函数返回字符串中右边的字符**

mysql> SELECT RIGHT('football', 4);
    +----------------------+
    | RIGHT('football', 4) |
    +----------------------+
    | ball                 |
    +----------------------+
    1 row in set (0.00 sec)

**十 使用LPAD函数对字符串进行填充操作**

mysql> SELECT LPAD('hello',4,'??'), LPAD('hello',10,'??');
    +----------------------+-----------------------+
    | LPAD('hello',4,'??') | LPAD('hello',10,'??') |
    +----------------------+-----------------------+
    | hell                 | ?????hello            |
    +----------------------+-----------------------+
    1 row in set (0.00 sec)

**十一 使用RPAD函数对字符串进行填充操作**

**十二 使用LTRIM函数删除字符串左边的空格**

mysql> SELECT '(  book  )',CONCAT('(',LTRIM('  book  '),')');
    +------------+-----------------------------------+
    | (  book  ) | CONCAT('(',LTRIM('  book  '),')') |
    +------------+-----------------------------------+
    | (  book  ) | (book  )                          |
    +------------+-----------------------------------+
    1 row in set (0.00 sec)

**十三 使用RTRIM函数删除字符串右边的空格**

mysql> SELECT '(  book  )',CONCAT('(', RTRIM ('  book  '),')');
    +------------+-------------------------------------+
    | (  book  ) | CONCAT('(', RTRIM ('  book  '),')') |
    +------------+-------------------------------------+
    | (  book  ) | (  book)                            |
    +------------+-------------------------------------+
    1 row in set (0.00 sec)

**十四 使用TRIM函数删除字符串两边的空格**

mysql> SELECT '(  book  )',CONCAT('(', TRIM('  book  '),')');
    +------------+-----------------------------------+
    | (  book  ) | CONCAT('(', TRIM('  book  '),')') |
    +------------+-----------------------------------+
    | (  book  ) | (book)                            |
    +------------+-----------------------------------+
    1 row in set (0.00 sec)

**十五 使用TRIM(s1 FROM s)函数删除字符串中两端指定的字符**

mysql> SELECT TRIM('xy' FROM 'xyxboxyokxxyxy') ;
    +----------------------------------+
    | TRIM('xy' FROM 'xyxboxyokxxyxy') |
    +----------------------------------+
    | xboxyokx                         |
    +----------------------------------+
    1 row in set (0.00 sec)

**十六 使用REPEAT函数重复生成相同的字符串**

mysql> SELECT REPEAT('MySQL', 3);
    +--------------------+
    | REPEAT('MySQL', 3) |
    +--------------------+
    | MySQLMySQLMySQL    |
    +--------------------+
    1 row in set (0.00 sec)

**十七 使用SPACE函数生成由空格组成的字符串**

mysql> SELECT CONCAT('(', SPACE(6), ')' );
    +-----------------------------+
    | CONCAT('(', SPACE(6), ')' ) |
    +-----------------------------+
    | (      )                    |
    +-----------------------------+
    1 row in set (0.00 sec)

**十八 使用REPLACE函数进行字符串替代操作**

mysql> SELECT REPLACE('xxx.mysql.com', 'x', 'w');
    +------------------------------------+
    | REPLACE('xxx.mysql.com', 'x', 'w') |
    +------------------------------------+
    | www.mysql.com                      |
    +------------------------------------+
    1 row in set (0.00 sec)

**十九 使用STRCMP函数比较字符串大小**

mysql> SELECT STRCMP('txt', 'txt2'),STRCMP('txt2', 'txt'), STRCMP('txt', 'txt');
    +-----------------------+-----------------------+----------------------+
    | STRCMP('txt', 'txt2') | STRCMP('txt2', 'txt') | STRCMP('txt', 'txt') |
    +-----------------------+-----------------------+----------------------+
    |                    -1 |                     1 |                    0 |
    +-----------------------+-----------------------+----------------------+
    1 row in set (0.00 sec)

**二十 使用SUBSTRING函数获取指定位置处的子字符串**

SELECT SUBSTRING('breakfast',5) AS col1,
    SUBSTRING('breakfast',5,3) AS col2,
    SUBSTRING('lunch', -3) AS col3,
    SUBSTRING('lunch', -5, 3) AS col4;
    +-------+------+------+------+
    | col1  | col2 | col3 | col4 |
    +-------+------+------+------+
    | kfast | kfa  | nch  | lun  |
    +-------+------+------+------+
    1 row in set (0.00 sec)

**二一 使用MID()函数获取指定位置处的子字符串**

SELECT MID('breakfast',5) as col1,
    MID('breakfast',5,3) as col2,
    MID('lunch', -3) as col3,
    MID('lunch', -5, 3) as col4;
    +-------+------+------+------+
    | col1  | col2 | col3 | col4 |
    +-------+------+------+------+
    | kfast | kfa  | nch  | lun  |
    +-------+------+------+------+
    1 row in set (0.00 sec)

**二二 使用LOCATE，POSITION，INSTR函数查找字符串中指定子字符串的开始位置**

mysql> SELECT LOCATE('ball','football'),POSITION('ball'IN 'football'),INSTR ('football', 'ball');
    +---------------------------+-------------------------------+----------------------------+
    | LOCATE('ball','football') | POSITION('ball'IN 'football') | INSTR ('football', 'ball') |
    +---------------------------+-------------------------------+----------------------------+
    |                         5 |                             5 |                          5 |
    +---------------------------+-------------------------------+----------------------------+

**二三 使用REVERSE函数反转字符串**

mysql> SELECT REVERSE('abc');
    +----------------+
    | REVERSE('abc') |
    +----------------+
    | cba            |
    +----------------+
    1 row in set (0.00 sec)

**二四 使用ELT函数返回指定位置字符串**

**二五 使用FIELD函数返回指定字符串第一次出现的位置**

mysql> SELECT FIELD('Hi', 'hihi', 'Hey', 'Hi', 'bas') as col1,FIELD('Hi', 'Hey', 'Lo', 'Hilo',  'foo') as col2;
    +------+------+
    | col1 | col2 |
    +------+------+
    |    3 |    0 |
    +------+------+
    1 row in set (0.00 sec)

**二六 使用FIND\_IN\_SET()函数返回子字符串在字符串列表中的位置**

mysql> SELECT FIND_IN_SET('Hi','hihi,Hey,Hi,bas');
    +-------------------------------------+
    | FIND_IN_SET('Hi','hihi,Hey,Hi,bas') |
    +-------------------------------------+
    |                                   3 |
    +-------------------------------------+
    1 row in set (0.00 sec)

**二七 使用MAKE\_SET根据二进制位选取指定字符串**

SELECT  MAKE_SET(1,'a','b','c') as col1,
         MAKE_SET(1 | 4,'hello','nice','world') as col2,
         MAKE_SET(1 | 4,'hello','nice',NULL,'world') as col3,
         MAKE_SET(0,'a','b','c') as col4;
    +------+-------------+-------+------+
    | col1 | col2        | col3  | col4 |
    +------+-------------+-------+------+
    | a    | hello,world | hello |      |
    +------+-------------+-------+------+
    1 row in set (0.00 sec)