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

字符函数大概分为两类：大小写控制函数(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')

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;

select cast(now() as char);

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

---

【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;

---

【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;

---

【7】字符串截取

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

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

语法格式如下：

left（str, length） 

说明：left（被截取字段，截取长度）

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

---

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

语法格式如下：

right（str, length） 

说明：right（被截取字段，截取长度）

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

---

③ 从索引位置截取字符串

substr（str, pos）

与SUBSTR(str FROM pos)功能一致，可以与concat组合使用。

#substr 下标从1开始，左右均为闭区间
update pm_product 
set spec_sku_attribute=concat('[',  '这里插入新值',  ',' , substr(spec_sku_attribute,1)) 
where id=10610 

substr（str, pos, length）

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

说明：

substr（被截取字段，从第几位开始截取） 

substr（被截取字段，从第几位开始截取，截取长度）

实例

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

从第五位开始截取3个字符

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

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

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

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

---

③ 按关键字截取字符串

语法格式如下：

substring_index（str,delim,count） 

说明：substring_index（被截取字段，关键字，关键字出现的次数）

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

---

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

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

---

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

如下实例：

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("中国");

---

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

语法格式如下

length(str);

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

语法格式如下

cha_length(str);

---

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

语法格式如下

INSTR (string ,substring ) 

示例如下：

|
返回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 ,'国' );

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

---

【14】LCASE(string)

将字符串转换为小写。

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

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

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

---

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

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

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

---

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

语法格式如下：

`REPLACE`(str,from_str,to_str);

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

示例如下：

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

---

【17】去除左端空格

语法格式如下：

LTRIM(str)

示例如下：

select ltrim(' abc ');

去除右端空格同上,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 ');

leading

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

trailing

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

---

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

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

若有，返回index；若无，返回 0。

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

---

【20】REPEAT(str,count )

将字符串重复count次。

select REPEAT('abc',3)

---

【21】space(count)

生成count个空格。

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

---

【22】LOAD_FILE(fileName)

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

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

---

【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

【24】replace

字符替换：

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