字符函数大概分为两类:大小写控制函数(LOWER,UPPER)和字符控制函数

大小写控制函数

函数

结果

LOWER(‘SQL Course’)

sql course

UPPER(‘SQL Course’)

SQL COURSE

这类函数改变字符的大小写。

字符控制函数:

  • CONCAT
  • SUBSTR
  • LENGTH
  • INSTR
  • LPAD | RPAD
  • TRIM
  • REPLACE

函数

结果

CONCAT(‘Hello’, ‘World’)

HelloWorld

SUBSTR(‘HelloWorld’,1,5)

Hello

LENGTH(‘HelloWorld’)

10

INSTR(‘HelloWorld’, ‘W’)

6

LPAD(‘salary’,10,’*’)

*****salary

RPAD(‘salary’,10,’*’)

salary*****

TRIM(‘H’ FROM’HelloWorld’)

elloWorld

REPLACE(‘abcd’,‘b’,‘m’)

amcd

【1】字符串拼接函数

将字符串直接拼接起来;

CONCAT(str1,str2,...)

Navicat 实例:

select * from tb_user where user_name like CONCAT('%','演示','%')

【2】添加分隔符的字符串拼接函数

将字符串通过分隔符拼接起来;

CONCAT_WS(separator,str1,str2,...)

Navicat 实例:

select CONCAT_WS(',','123456','45','9')

MySQL中常用函数之字符串函数使用详解_应用
contact与substr组合使用

update pm_product  set spec_sku_attribute 
=concat(substr(spec_sku_attribute,1,char_length(spec_sku_attribute)-1),
',','这里插入新值',']') where id=10610

#substr 下标从1开始,左右均为闭区间

【3】格式化函数

不只是格式化日期

CONVERT(expr,type);
or
CAST(expr AS type)
//二者功能相似

The type can be one of the following values :

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

Navicat 实例:

select   
CONVERT(SYSDATE(),datetime) as datetime ,
CONVERT(SYSDATE(),date) as date from dual;

MySQL中常用函数之字符串函数使用详解_实例_02

select cast(now() as char);

select cast((1/3)*100 as UNSIGNED) as percent from dual;
--result will be 33

MySQL中常用函数之字符串函数使用详解_函数_03


【4】带编码的格式化函数

`CONVERT`(expr USING transcoding_name)

Navicat 实例:

select CONVERT('duifek' USING 'utf8')

【5】字符串转日期

STR_TO_DATE(str,format)

Navicat 实例:

select 
STR_TO_DATE('2012-02-02 12:12:12','%Y-%m-%d') as date ,
STR_TO_DATE('2012-02-02 12:12:12','%Y-%m-%d %H:%i:%s') as datetime
from dual;

MySQL中常用函数之字符串函数使用详解_mysql_04


【6】日期格式化

日期格式化专用函数

DATE_FORMAT(date,format)

Navicat 实例:

select 
DATE_FORMAT(SYSDATE(),'%Y-%m-%d') as date,
DATE_FORMAT(SYSDATE(),'%Y-%m-%d %H:%i:%s') as datetime
from dual;

MySQL中常用函数之字符串函数使用详解_字符串_05


【7】字符串截取

MySQL中处理字符串时,默认第一个字符下标为1 ,而不是0。

①从左开始截取指定长度字符串

语法格式如下:

left(str, length)

说明:left(被截取字段,截取长度)

//从左开始截取六个字符
select left('15993729750',6) from dual;

MySQL中常用函数之字符串函数使用详解_实例_06


② 从右开始截取指定长度字符串

语法格式如下:

right(str, length)

说明:right(被截取字段,截取长度)

//从右开始截取六个字符
select right('15993729750',6) from dual;

MySQL中常用函数之字符串函数使用详解_mysql_07


③ 从索引位置截取字符串

substr(str, pos)

与SUBSTR(str FROM pos)功能一致

substr(str, pos, length)

与SUBSTR(str FROM pos FOR len)功能一致

说明:

substr(被截取字段,从第几位开始截取) 

substr(被截取字段,从第几位开始截取,截取长度)

实例

//从第五位开始截取字符串--包含第五位
select substr('15993729750',5) from dual;

MySQL中常用函数之字符串函数使用详解_应用_08
从第五位开始截取3个字符

select substr('15993729750',5,3) from dual;

MySQL中常用函数之字符串函数使用详解_字符串_09

如果位数是负数 如-5 则是从后倒数位数,到字符串结束或截取的长度

select substr('15993729750',-5,3) from dual;

MySQL中常用函数之字符串函数使用详解_字符串_10

select substr('15993729750',-5) from dual;

MySQL中常用函数之字符串函数使用详解_实例_11


③ 按关键字截取字符串

语法格式如下:

substring_index(str,delim,count)

说明:substring_index(被截取字段,关键字,关键字出现的次数)

select SUBSTRING_INDEX('1599372123456','3','2') from dual;

MySQL中常用函数之字符串函数使用详解_应用_12


另外,MD5()函数可以在插入数据的时候进行md5加密:

select md5(SUBSTRING('1599372123456',-6)) from dual;

MySQL中常用函数之字符串函数使用详解_实例_13


字符串截取是可以组合使用的

如下实例:

update shop_design set CATEGORY_PARAM=(
select CONCAT(substring_index(CATEGORY_PARAM,"{\"cond_type\":\"=\",\"value\":\"1\",\"name\":\"STATE\"}","1"),"\"1\"\,\"",
substr(CATEGORY_PARAM,INSTR (CATEGORY_PARAM ,"RELEASE_DATE" ))
))

INSTR (CATEGORY_PARAM ,“RELEASE_DATE” )是拿到子字符串(后面)在父字符串(前面)的下标位置。


【8】查看字符串字符集

语法格式如下

charset(str);

Navicat 实例:

select CHARSET("中国");

MySQL中常用函数之字符串函数使用详解_字符串_14


【9】查看字符串字节长度

语法格式如下

length(str);

【10】查看字符串字符长度

语法格式如下

cha_length(str);

【11】判断字符串是否包含某个字符串

语法格式如下

INSTR (string ,substring )

示例如下:

MySQL中常用函数之字符串函数使用详解_字符串_15
|
返回substring首次在string中出现的位置,不存在返回0。


【12】字符串比较函数

字符串比较是逐字符比较,不区分大小写。

select STRCMP ("中国你好" ,"中国你好" ) ;

相等返回0;大于返回 1 ;小于 返回 -1 。

【13】lpad (str ,length ,pad )

左填充,将字符串按照某个指定的填充方式,填充到指定长度(字符,即中英无关)。

即,重复用pad加在string开头,直到字串长度为length。

select  lpad ('hello' ,11 ,'ar' ),lpad ('hello' ,11 ,'国' );

MySQL中常用函数之字符串函数使用详解_函数_16

同样存在的还有右填充,​​rpad (string2 ,length ,pad )​​,其他说明同上。


【14】​​LCASE(string)​

将字符串转换为小写。

select  LCASE('ABC'),LCASE('abcd'),LCASE('abDEd')

MySQL中常用函数之字符串函数使用详解_应用_17

将字符串转换成大写如下:

select ucase(' abc '),UCASE('aBC')

MySQL中常用函数之字符串函数使用详解_实例_18


【15】去除字符串两端空格

只能去掉两端,不能去掉中间。

SELECT TRIM(' a b c ');
or
SELECT trim(' ' FROM ' a b c ');

MySQL中常用函数之字符串函数使用详解_函数_19


【16】去除字符串所有空格

语法格式如下:

`REPLACE`(str,from_str,to_str);

-- 不只是可以去掉所有空格,replace函数意思用指定to_str替换掉字符串中所有的from_str.

示例如下:

SELECT replace(' a b c ',' ','');

MySQL中常用函数之字符串函数使用详解_应用_20


【17】去除左端空格

语法格式如下:

LTRIM(str)

示例如下:

select ltrim(' abc ');

MySQL中常用函数之字符串函数使用详解_函数_21

去除右端空格同上,​​rtrim(' abc ')​​。

于此,也可实现去除字符串两端空格。

select RTRIM(LTRIM(' abc '));
or
select LTRIM(RTRIM(' abc '));

同样的问题,不能去除字符串内部的空格。


【18】去除指定位置字符串。

TRIM([[BOTH|LEADING|TRAILING] [str]FROM]string);

both : 从字符串两端去除指定str;
leading:从左(前)端去除指定str;
trailing:从右(后)端去除指定str;

both:

SELECT trim(both ' ' FROM ' a b c ');
SELECT RTRIM(LTRIM(' a b c '));
SELECT trim(' a b c ');

MySQL中常用函数之字符串函数使用详解_字符串_22

leading

SELECT TRIM(LEADING ' ' FROM ' a b c '),LTRIM(' a b c ');

MySQL中常用函数之字符串函数使用详解_mysql_23

trailing

SELECT TRIM(trailing ' ' FROM ' a b c '),RTRIM(' a b c ');

MySQL中常用函数之字符串函数使用详解_实例_24


【19】​​LOCATE (substr,str[,position])​

从指定位置开始判断字符串中是否存在某个字符串,默认从index=1(第一位)开始查找。

若有,返回index;若无,返回 0。

select LOCATE('abc','abcagabcde'),LOCATE('abc','abcagabcde',2);

MySQL中常用函数之字符串函数使用详解_应用_25


【20】​​REPEAT(str,count )​

将字符串重复count次。

select REPEAT('abc',3)

MySQL中常用函数之字符串函数使用详解_应用_26


【21】space(count)

生成count个空格。

SELECT concat('abc',repeat(' ',3),'d',SPACE(0));

MySQL中常用函数之字符串函数使用详解_实例_27


【22】​​LOAD_FILE(fileName)​

读取指定路径下的文件到内存。

SELECT LOAD_FILE('D:/temDirectory/backup.txt') ;

MySQL中常用函数之字符串函数使用详解_实例_28


【23】insert(str,pos,len,newstr)

select 
insert('abcdefg',3,3,'中国人好'),-- 从index开始的三个字符被newstr替换

insert('abcdefg',30,3,'中国人好'),-- index大于字符串长度,返回原字符串

insert('abcdefg',3,30,'中国人好'),-- 从index开始的所有字符被newstr替换

insert('abcdefg',null,3,'中国人好'),-- index 为null 返回null

insert('abcdefg',3,null,'中国人好'),-- pos 为null 返回null

insert('abcdefg','',3,'中国人好'),-- 无index 返回原字符串

insert('abcdefg','','','中国人好'),-- 无index 无pos 返回原字符串

insert('abcdefg',3,'','中国人好');-- 从index开始的位置插入newstr

MySQL中常用函数之字符串函数使用详解_函数_29

【24】replace

字符替换:

replace(字段名,需要替换的字符串,目标字符串)