MySQL中的可用函数

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

点击(此处)折叠或打开

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

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

点击(此处)折叠或打开

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

截取字段前200字符

点击(此处)折叠或打开

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

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

点击(此处)折叠或打开

1. update doctor set hospdept_uuid = substring_index(hospdept_uuid,',',1),gmt_modified =now() where hospdept_uuid like '%,%';
2. ##截取某个字符串前后数据,更新到其他字段
3. 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个长度的字符串

点击(此处)折叠或打开

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

去除字段首尾空格

点击(此处)折叠或打开

1. UPDATE gd_recommend SET expert_name=TRIM(TRAILING ' ' FROM expert_name);
   
2. 
   
3. UPDATE gd_recommend SET expert_name=TRIM(LEADING ' ' FROM expert_name);
   
4. 
   
5. UPDATE gd_recommend SET expert_name=TRIM(BOTH ' ' FROM expert_name);

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

点击(此处)折叠或打开

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

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

点击(此处)折叠或打开

1. 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博客 ” ，链接：http://blog.itpub.net/29143715/viewspace-1767497/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29143715/viewspace-1767497/