常用SQL语句及EXCLE公式
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
//excle替换 =SUBSTITUTE(A1,"111111","<p>") =SUBSTITUTE(A1,"333333","</p>") //kiss替换 update post set content=REPLACE(content,'<p>','<div><h3>'); update post set content=REPLACE(content,'222222','</h3><p>'); update post set content=REPLACE(content,'</p>','</p></div>'); update post set content=REPLACE(content,'Seven Trust','<strong>Seven Trust</strong>'); update post set content=REPLACE(content,'WPC Decking','<strong>WPC Decking</strong>'); update post set content=REPLACE(content,'Outdoor Decking','<strong>Outdoor Decking</strong>'); update post set content=REPLACE(content,'Composite','<em>Composite</em>'); //kiss口罩替换 update post set content=REPLACE(content,'N95 Masks','<strong>N95 Masks</strong>'); update post set content=REPLACE(content,'MeltBlown Fabric','<strong>MeltBlown Fabric</strong>'); update post set content=REPLACE(content,'Face Masks','<strong>Face Masks</strong>'); update post set content=REPLACE(content,'Eye Goggles','<em>Eye Goggles</em>'); |
https://zhuanlan.zhihu.com/p/64976011
https://zhuanlan.zhihu.com/p/28195028
|
1 |
=PHONETIC(c1:z1) |
|
1 |
=len() |
常用SQL语句
（一）删除
删除包含某字符的数据
|
1 2 3 4 |
select * from 【表名】 where 【字段名】like '%李明%' select * from musu20200617 where "关键词" like '%deck%' ORDER BY RANDOM() LIMIT 200000 //删除之前先查询一下然后 delete from 【表名】 where 【字段名】like '%李明%' |
删除重复项
|
1 2 3 4 5 6 7 8 9 |
//查询重复的项 SELECT title FROM post GROUP BY title HAVING COUNT(title)>1 //查询要删除的项 SELECT MIN(rowid) rowid FROM post GROUP BY title HAVING COUNT(title)>1 //删除重复项 DELETE FROM post WHERE 1=1 AND `title` in (SELECT * FROM ( (SELECT title FROM post GROUP BY title HAVING COUNT(title)>1) ) a) AND posttime not in (SELECT * FROM ( (SELECT MIN(posttime) posttime FROM post GROUP BY title HAVING COUNT(title)>1) ) b) |
https://blog.csdn.net/a11085013/article/details/8549256
https://www.cnblogs.com/java-zzl/p/9739750.html
（二）替换
替换字符串
|
1 2 3 |
//kiss update post set title=REPLACE(title,' ',' '); update post set content=REPLACE(content,' ',' '); |
常用EXCLE公式
（1）大小写转小写
|
1 2 3 4 5 6 |
//转小写 =LOWER(A1) //全部大写 =UPPER(A1) //首字母转大写 =PROPER(A1) |
（2）去除换行 & 添加换行
|
1 2 3 4 |
//去除 =CLEAN(a1) //添加 =SUBSTITUTE(A1,"</p>","</p>"&CHAR(10)) |
（3）去除多余空格
|
1 |
=TRIM(A1) |
（4）字符串替换
|
1 |
=SUBSTITUTE(A1,"Subfloors","SevenTrust") |
（5）去除非打印字符（去除双引号）
|
1 |
=clean(a1) |
（6）截取固定长度字符串
|
1 2 3 4 5 6 |
//A行内从第一个字符开始（包含第一个字），截取长度为四个字符的字符串。 =MID(A2,1,4) //截取固定长度字符串2：截取A行内从第一个字符开始（包含第一个字）到第一个</p>符号前内容（必须要-1，否则会有半个<）.find控制范围 =MID(B3,1,FIND("</p>",B3)-1) // =MID(A1,FIND("|||",A1)+3,LEN(A1)) //截取|||前内容 =LEFT(A1,FIND("|||",A1)-1) |
（7）标记/删除重复项
|
1 2 3 4 5 |
//不能全部删除重复的，比较范围小 =IF(ISERROR(VLOOKUP(A2,A1:A$2,1,0)),"","要删除") =IF(COUNTIF(A$1:A2,A2)>1,"重复","") //上下比较，需要排序 =if(a1=a2,"" ,a1) |
（8）是否包含中文
=LEN(B2)-LENB(B2)
已是最后文章
下一篇: 老数据合并
总计 0 评论