替换字段col 中的字符串 str1 为 str2
REPLACE(col,str1,str2)

点击(此处)折叠或打开

update article a set content_abbr=REPLACE(content_abbr,\'<\',\'\');

从字段 col指定位置 fir开始，替换指定长度 len的字符串为 str
INSERT(col,fir,len,str)

点击(此处)折叠或打开

UPDATE article a,(SELECT INSERT(content_abbr,fir,sec-fir+1,\'\') str2,id FROM
(SELECT POSITION(\'<\' IN content_abbr) fir,POSITION(\'>\' IN content_abbr) sec,content_abbr,id FROM article) t) t2
SET a.content_abbr = t2.str2 WHERE a.id=t2.id;

截取字段前200字符

点击(此处)折叠或打开

UPDATE article a,working.art2 b SET a.cont_abbr=SUBSTR(b.cont,1,200) WHERE a.id=b.id

截取某个字符之前的字符串

点击(此处)折叠或打开

update doctor set hospdept_uuid = substring_index(hospdept_uuid,',',1),gmt_modified =now() where hospdept_uuid like '%,%';
##截取某个字符串前后数据,更新到其他字段
UPDATE order_offline_apply
SET start_intent_clinic_date = STR_TO_DATE(SUBSTRING_INDEX(intent_clinic_date,'~',1),'%Y-%m-%d'),
end_intent_clinic_date = STR_TO_DATE(SUBSTRING_INDEX(intent_clinic_date,'~', - 1),'%Y-%m-%d'),
gmt_modified= NOW()
WHERE intent_clinic_date LIKE '%~%';

截取指定字符串之后n个长度的字符串

点击(此处)折叠或打开

SELECT DISTINCT(SUBSTRING(content_abbr,INSTR(content_abbr,'&'),6)) FROM article0806 WHERE content_abbr LIKE '%&%';

去除字段首尾空格

点击(此处)折叠或打开

UPDATE gd_recommend SET expert_name=TRIM(TRAILING ' ' FROM expert_name);
UPDATE gd_recommend SET expert_name=TRIM(LEADING ' ' FROM expert_name);
UPDATE gd_recommend SET expert_name=TRIM(BOTH ' ' FROM expert_name);

以逗号分隔字段的列转行操作

点击(此处)折叠或打开

SELECT a.ID,SUBSTRING_INDEX(SUBSTRING_INDEX(a.fun_auth_config,',',b.help_topic_id+1),',',-1)
FROM
doctor a
JOIN
mysql.help_topic b
ON b.help_topic_id < (LENGTH(a.fun_auth_config) - LENGTH(REPLACE(a.fun_auth_config,',',''))+1)
ORDER BY a.ID;

查询varchar 字段长度，以及字符个数

点击(此处)折叠或打开

select length('你好2015') '字节数',char_length('你好2015') '字符个数' from dual;

查询并替换字段中的回车/换行符（代码怎么插不进去啦？没人维护了吗？）
SELECT * FROM table_0217 WHERE disease_id LIKE '%\n';
SELECT * FROM table_0217 WHERE disease_id LIKE '%\r';
update table_0217 set disease_id = replace(disease_id,'char(13)','');
update table_0217 set disease_id = replace(disease_id,'char(10)','');

ITPub博客

MySQL中的可用函数

最新文章