MariaDB [mimvp]> select length('mimvp.com'); +---------------------+ | length('mimvp.com') | +---------------------+ | 9 | +---------------------+ MariaDB [mimvp]> select length('米扑科技'); +------------------------+ | length('米扑科技') | +------------------------+ | 12 | +------------------------+ MariaDB [mimvp]> select length(123456); +----------------+ | length(123456) | +----------------+ | 6 | +----------------+ MariaDB [mimvp]> select name, length(name), char_length(name) from mimvp; +--------------+--------------+-------------------+ | name | length(name) | char_length(name) | +--------------+--------------+-------------------+ | 123456 | 6 | 6 | | mimvp.com | 9 | 9 | | 米扑代理 | 12 | 4 | | 米扑导航 | 12 | 4 | | 米扑域名 | 12 | 4 | | 米扑财富 | 12 | 4 | +--------------+--------------+-------------------+ MariaDB [mimvp]> select concat('mimvp','.com',2016); +-----------------------------+ | concat('mimvp','.com',2016) | +-----------------------------+ | mimvp.com2016 | +-----------------------------+ MariaDB [mimvp]> select concat('mimvp','.com','-2016'); +--------------------------------+ | concat('mimvp','.com','-2016') | +--------------------------------+ | mimvp.com-2016 | +--------------------------------+ MariaDB [mimvp]> select concat('mimvp',null,'-2016'); +------------------------------+ | concat('mimvp',null,'-2016') | +------------------------------+ | NULL | +------------------------------+ MariaDB [mimvp]> update mimvp set name=concat('米扑科技 - ', name) where name like '米扑%'; Query OK, 4 rows affected (0.01 sec) MariaDB [mimvp]> select * from mimvp; +------+-----------------------------+------+ | id | name | age | +------+-----------------------------+------+ | 1 | 123456 | 2 | | 2 | mimvp.com | 3 | | 3 | 米扑科技 - 米扑代理 | 3 | | 4 | 米扑科技 - 米扑导航 | 3 | | 5 | 米扑科技 - 米扑域名 | 5 | | 6 | 米扑科技 - 米扑财富 | 8 | +------+-----------------------------+------+ MariaDB [mimvp]> update mimvp set name=concat(name, ' - 2016') where name like '米扑%'; Query OK, 4 rows affected (0.01 sec) MariaDB [mimvp]> select * from mimvp; +------+------------------------------------+------+ | id | name | age | +------+------------------------------------+------+ | 1 | 123456 | 2 | | 2 | mimvp.com | 3 | | 3 | 米扑科技 - 米扑代理 - 2016 | 3 | | 4 | 米扑科技 - 米扑导航 - 2016 | 3 | | 5 | 米扑科技 - 米扑域名 - 2016 | 5 | | 6 | 米扑科技 - 米扑财富 - 2016 | 8 | +------+------------------------------------+------+ MariaDB [mimvp]> select left('mimvp.com',5); +---------------------+ | left('mimvp.com',5) | +---------------------+ | mimvp | +---------------------+ MariaDB [mimvp]> select left('米扑科技 - 米扑代理 - 2016',3); +----------------------------------------------+ | left('米扑科技 - 米扑代理 - 2016',3) | +----------------------------------------------+ | 米扑科 | +----------------------------------------------+ MariaDB [mimvp]> select left('123456',3); +------------------+ | left('123456',3) | +------------------+ | 123 | +------------------+ MariaDB [mimvp]> select right('mimvp.com',5); +----------------------+ | right('mimvp.com',5) | +----------------------+ | p.com | +----------------------+ MariaDB [mimvp]> select right('米扑科技 - 米扑代理',3); +----------------------------------------+ | right('米扑科技 - 米扑代理',3) | +----------------------------------------+ | 扑代理 | +----------------------------------------+ MariaDB [mimvp]> select right('123456',3); +-------------------+ | right('123456',3) | +-------------------+ | 456 | +-------------------+ MariaDB [mimvp]> select substring('米扑科技 - 米扑代理 - 2016', 1, 4); +-------------------------------------------------------+ | substring('米扑科技 - 米扑代理 - 2016', 1, 4) | +-------------------------------------------------------+ | 米扑科技 | +-------------------------------------------------------+ MariaDB [mimvp]> select substring('米扑科技 - 米扑代理 - 2016', 1); +----------------------------------------------------+ | substring('米扑科技 - 米扑代理 - 2016', 1) | +----------------------------------------------------+ | 米扑科技 - 米扑代理 - 2016 | +----------------------------------------------------+ MariaDB [mimvp]> select substring('米扑科技 - 米扑代理 - 2016' from 1 for 4); +--------------------------------------------------------------+ | substring('米扑科技 - 米扑代理 - 2016' from 1 for 4) | +--------------------------------------------------------------+ | 米扑科技 | +--------------------------------------------------------------+ MariaDB [mimvp]> select substring('米扑科技 - 米扑代理 - 2016' from 1); +--------------------------------------------------------+ | substring('米扑科技 - 米扑代理 - 2016' from 1) | +--------------------------------------------------------+ | 米扑科技 - 米扑代理 - 2016 | +--------------------------------------------------------+ MariaDB [mimvp]> select substr('米扑科技 - 米扑代理 - 2016', 1, 4); +----------------------------------------------------+ | substr('米扑科技 - 米扑代理 - 2016', 1, 4) | +----------------------------------------------------+ | 米扑科技 | +----------------------------------------------------+ MariaDB [mimvp]> select substr('米扑科技 - 米扑代理 - 2016', 1); +-------------------------------------------------+ | substr('米扑科技 - 米扑代理 - 2016', 1) | +-------------------------------------------------+ | 米扑科技 - 米扑代理 - 2016 | +-------------------------------------------------+ MariaDB [mimvp]> select mid('米扑科技 - 米扑代理 - 2016', 1, 4); +-------------------------------------------------+ | mid('米扑科技 - 米扑代理 - 2016', 1, 4) | +-------------------------------------------------+ | 米扑科技 | +-------------------------------------------------+ MariaDB [mimvp]> select mid('米扑科技 - 米扑代理 - 2016', 1); +----------------------------------------------+ | mid('米扑科技 - 米扑代理 - 2016', 1) | +----------------------------------------------+ | 米扑科技 - 米扑代理 - 2016 | +----------------------------------------------+ MariaDB [mimvp]> select * from mimvp; +------+------------------------------------+------+ | id | name | age | +------+------------------------------------+------+ | 1 | 123456 | 2 | | 2 | mimvp.com | 3 | | 3 | 米扑科技 - 米扑代理 - 2016 | 3 | | 4 | 米扑科技 - 米扑导航 - 2016 | 3 | | 5 | 米扑科技 - 米扑域名 - 2016 | 5 | | 6 | 米扑科技 - 米扑财富 - 2016 | 8 | +------+------------------------------------+------+ MariaDB [mimvp]> update mimvp set name = substring(name, 8) where name like '米扑科技 - %'; Query OK, 4 rows affected (0.00 sec) MariaDB [mimvp]> select * from mimvp; +------+---------------------+------+ | id | name | age | +------+---------------------+------+ | 1 | 123456 | 2 | | 2 | mimvp.com | 3 | | 3 | 米扑代理 - 2016 | 3 | | 4 | 米扑导航 - 2016 | 3 | | 5 | 米扑域名 - 2016 | 5 | | 6 | 米扑财富 - 2016 | 8 | +------+---------------------+------+ MariaDB [mimvp]> select left(name, (char_length(name)-char_length(' - 2016'))) from mimvp where name like '% - 2016'; +--------------------------------------------------------+ | left(name, (char_length(name)-char_length(' - 2016'))) | +--------------------------------------------------------+ | 米扑代理 | | 米扑导航 | | 米扑域名 | | 米扑财富 | +--------------------------------------------------------+ MariaDB [mimvp]> update mimvp set name = left(name, (char_length(name)-char_length(' - 2016'))) where name like '% - 2016'; Query OK, 4 rows affected (0.01 sec) MariaDB [mimvp]> select * from mimvp; +------+--------------+------+ | id | name | age | +------+--------------+------+ | 1 | 123456 | 2 | | 2 | mimvp.com | 3 | | 3 | 米扑代理 | 3 | | 4 | 米扑导航 | 3 | | 5 | 米扑域名 | 5 | | 6 | 米扑财富 | 8 | +------+--------------+------+ MariaDB [mimvp]> select replace('mimvp.com', 'mimvp', 'mimji'); +----------------------------------------+ | replace('mimvp.com', 'mimvp', 'mimji') | +----------------------------------------+ | mimji.com | +----------------------------------------+ MariaDB [mimvp]> update mimvp set name = replace(name, '米扑', '米积') where name like '米扑%'; Query OK, 4 rows affected (0.01 sec) Rows matched: 4 Changed: 4 Warnings: 0 MariaDB [mimvp]> select * from mimvp; +------+--------------+------+ | id | name | age | +------+--------------+------+ | 1 | 123456 | 2 | | 2 | mimvp.com | 3 | | 3 | 米积代理 | 3 | | 4 | 米积导航 | 3 | | 5 | 米积域名 | 5 | | 6 | 米积财富 | 8 | +------+--------------+------+ 字符串的替换，也可间接用作删除字段的前缀、后缀 如果字符串str最左面字符是一个多字节字符，通过以格式((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]返回字符的ASCII代码值来返回多字节字符代码。如果最左面的字符不是一个多字节字符，返回与ASCII()函数返回的相同值。 MariaDB [mimvp]> select ascii(0), ord(0); +----------+--------+ | ascii(0) | ord(0) | +----------+--------+ | 48 | 48 | +----------+--------+ MariaDB [mimvp]> select ascii(''), ord(''); +-----------+---------+ | ascii('') | ord('') | +-----------+---------+ | 0 | 0 | +-----------+---------+ MariaDB [mimvp]> select ascii(null), ord(null); +-------------+-----------+ | ascii(null) | ord(null) | +-------------+-----------+ | NULL | NULL | +-------------+-----------+ MariaDB [mimvp]> select ascii('abc'), ord('abc'); +--------------+------------+ | ascii('abc') | ord('abc') | +--------------+------------+ | 97 | 97 | +--------------+------------+ MariaDB [mimvp]> select ascii('米扑'), ord('米扑'); +-----------------+---------------+ | ascii('米扑') | ord('米扑') | +-----------------+---------------+ | 231 | 15184307 | +-----------------+---------------+ MariaDB [mimvp]> select conv('a', 16, 10), conv('a', 16, 8), conv('a', 16, 2), conv('a', 16, 1); +-------------------+------------------+------------------+------------------+ | conv('a', 16, 10) | conv('a', 16, 8) | conv('a', 16, 2) | conv('a', 16, 1) | +-------------------+------------------+------------------+------------------+ | 10 | 12 | 1010 | NULL | +-------------------+------------------+------------------+------------------+ MariaDB [mimvp]> select conv(100, 10, 16), conv(100, 10, 8), conv(100, 10, 2), conv(100, 10, 1); +-------------------+------------------+------------------+------------------+ | conv(100, 10, 16) | conv(100, 10, 8) | conv(100, 10, 2) | conv(100, 10, 1) | +-------------------+------------------+------------------+------------------+ | 64 | 144 | 1100100 | NULL | +-------------------+------------------+------------------+------------------+ MariaDB [mimvp]> select bin(100), oct(100), hex(100); +----------+----------+----------+ | bin(100) | oct(100) | hex(100) | +----------+----------+----------+ | 1100100 | 144 | 64 | +----------+----------+----------+ MariaDB [mimvp]> select char(48, 65, 97, 100); +-----------------------+ | char(48, 65, 97, 100) | +-----------------------+ | 0Aad | +-----------------------+ MariaDB [mimvp]> select char(48.5, 65.4, 97.8, null); +------------------------------+ | char(48.5, 65.4, 97.8, null) | +------------------------------+ | 1Ab | +------------------------------+ MariaDB [mimvp]> select length('mimvp'), char_length('mimvp'), character_length('mimvp'), octet_length('mimvp'); +-----------------+----------------------+---------------------------+-----------------------+ | length('mimvp') | char_length('mimvp') | character_length('mimvp') | octet_length('mimvp') | +-----------------+----------------------+---------------------------+-----------------------+ | 5 | 5 | 5 | 5 | +-----------------+----------------------+---------------------------+-----------------------+ MariaDB [mimvp]> select length('mimvp-米扑科技'), char_length('mimvp-米扑科技'), character_length('mimvp-米扑科技'), octet_length('mimvp-米扑科技'); +------------------------------+-----------------------------------+----------------------------------------+------------------------------------+ | length('mimvp-米扑科技') | char_length('mimvp-米扑科技') | character_length('mimvp-米扑科技') | octet_length('mimvp-米扑科技') | +------------------------------+-----------------------------------+----------------------------------------+------------------------------------+ | 18 | 10 | 10 | 18 | +------------------------------+-----------------------------------+----------------------------------------+------------------------------------+ MariaDB [mimvp]> select locate('mvp','mimvp-mvp'), locate('mvp','mimvp-mvp',4), locate('mip','mimvp'); +---------------------------+-----------------------------+-----------------------+ | locate('mvp','mimvp-mvp') | locate('mvp','mimvp-mvp',4) | locate('mip','mimvp') | +---------------------------+-----------------------------+-----------------------+ | 3 | 7 | 0 | +---------------------------+-----------------------------+-----------------------+ MariaDB [mimvp]> select instr('mimvp-mvp','mvp'), instr('mimvp','mip'); +--------------------------+----------------------+ | instr('mimvp-mvp','mvp') | instr('mimvp','mip') | +--------------------------+----------------------+ | 3 | 0 | +--------------------------+----------------------+ MariaDB [mimvp]> select lpad('mi', 5, '0'), rpad('mi', 5, '0'); +--------------------+--------------------+ | lpad('mi', 5, '0') | rpad('mi', 5, '0') | +--------------------+--------------------+ | 000mi | mi000 | +--------------------+--------------------+ SUBSTRING_INDEX(str, delim, count) 返回从字符串str的第count个出现的分隔符delim之后的子串。如果count是正数，返回最后的分隔符到左边(从左边数) 的所有字符。如果count是负数，返回最后的分隔符到右边的所有字符(从右边数)。  MariaDB [mimvp]> select substring_index('www.mimvp.com', '.', 2), substring_index('www.mimvp.com', '.', -2); +------------------------------------------+-------------------------------------------+ | substring_index('www.mimvp.com', '.', 2) | substring_index('www.mimvp.com', '.', -2) | +------------------------------------------+-------------------------------------------+ | www.mimvp | mimvp.com | +------------------------------------------+-------------------------------------------+ TRIM([BOTH | LEADING | TRAILING] [ remstr ] FROM] str)   返回字符串str，其所有remstr前缀或后缀被删除了。如果没有修饰符BOTH、LEADING或TRAILING给出，BOTH被假定。如果remstr没被指定，默认为空格被删除。 MariaDB [mimvp]> select trim(' mimvp - tech '), trim('x' from ' xx mimvp xx'), trim('x' from 'xxmimvpxx'); +------------------------+-------------------------------+----------------------------+ | trim(' mimvp - tech ') | trim('x' from ' xx mimvp xx') | trim('x' from 'xxmimvpxx') | +------------------------+-------------------------------+----------------------------+ | mimvp - tech | xx mimvp | mimvp | +------------------------+-------------------------------+----------------------------+ MariaDB [mimvp]> select trim(leading 'x' from 'xxxmimvpxx'), trim(both 'x' from 'xxxmimvpxx'), trim(trailing 'x' from 'xxxmimvpxx'); +-------------------------------------+----------------------------------+--------------------------------------+ | trim(leading 'x' from 'xxxmimvpxx') | trim(both 'x' from 'xxxmimvpxx') | trim(trailing 'x' from 'xxxmimvpxx') | +-------------------------------------+----------------------------------+--------------------------------------+ | mimvpxx | mimvp | xxxmimvp | +-------------------------------------+----------------------------------+--------------------------------------+ MariaDB [mimvp]> select reverse('mimvp'), reverse(123456), reverse('米扑科技'); +------------------+-----------------+-------------------------+ | reverse('mimvp') | reverse(123456) | reverse('米扑科技') | +------------------+-----------------+-------------------------+ | pvmim | 654321 | 技科扑米 | +------------------+-----------------+-------------------------+ MariaDB [mimvp]> select space(5), repeat('mimvp', 3), insert('mimvp.com', 4, 2, 'ji'), replace('mimvp.com', 'mimvp', 'mimji'); +----------+--------------------+---------------------------------+----------------------------------------+ | space(5) | repeat('mimvp', 3) | insert('mimvp.com', 4, 2, 'ji') | replace('mimvp.com', 'mimvp', 'mimji') | +----------+--------------------+---------------------------------+----------------------------------------+ | | mimvpmimvpmimvp | mimji.com | mimji.com | +----------+--------------------+---------------------------------+----------------------------------------+ MariaDB [mimvp]> select elt(3, 'mi', 'mvp', 'mimvp'), field('mvp', 'mi', 'mvp', 'mimvp') ; +------------------------------+------------------------------------+ | elt(3, 'mi', 'mvp', 'mimvp') | field('mvp', 'mi', 'mvp', 'mimvp') | +------------------------------+------------------------------------+ | mimvp | 2 | +------------------------------+------------------------------------+ 返回一个集合 (包含由“,”字符分隔的子串组成的一个字符串)，由相应的位在bits集合中的的字符串组成。str1对应于位0，str2对应位1，等等。在str1, str2, ...中的NULL串不添加到结果中。 MariaDB [mimvp]> select make_set(1,'a','b','c'), make_set(1 | 3,'a','b','c'), make_set(1 | 0,'a','b','c'); +-------------------------+-----------------------------+-----------------------------+ | make_set(1,'a','b','c') | make_set(1 | 3,'a','b','c') | make_set(1 | 0,'a','b','c') | +-------------------------+-----------------------------+-----------------------------+ | a | a,b | a | +-------------------------+-----------------------------+-----------------------------+ MariaDB [mimvp]> select lower('miMVP'), lcase('miMVP'), upper('miMVP'), ucase('miMVP'); +----------------+----------------+----------------+----------------+ | lower('miMVP') | lcase('miMVP') | upper('miMVP') | ucase('miMVP') | +----------------+----------------+----------------+----------------+ | mimvp | mimvp | MIMVP | MIMVP | +----------------+----------------+----------------+----------------+ 2018-03-19 05:15:54

侵权处理 : 本个人博客，不盈利，若侵犯了您的作品权，请联系博主删除，莫恶意，索钱财，感谢！

转载注明 : MySQL 字段的添加前缀、去掉前缀等字符串操作 （米扑博客） 原文链接 : https://blog.mimvp.com/article/24247.html