EXCEL-SQL学习笔记——replace函数

如图，现需要将襄樊市更改为襄阳市

SQL如下

select 经销商地址,replace(经销商地址,"襄樊市","襄阳市") as 变更后地址 from [襄樊部分企业$]

一、replace函数的使用

replace返回一个字符串，字符串中的指定字符串已经被特定字符串替换了一次或者多次，语法如下

replace(expression,find,replace[,start][,count][,compare])

• 在字符串(expression)中找到字符串(find)替换成目标字符串(replace),star为起始位置，count为要替换的次数，compare用于设置比较类型。其中后面三个都为可选参数

1、返回值说明

条件	replace返回的结果
若expression长度为零	零长度字符串("")
若expression长度为Null	错误
若find长度为零	expression的副本
若replace长度为零	expression的副本，其中删除了出现的所有find
若start>Len(expression)	零长度字符串
若count为0	expression的副本

2、例子

①现要将如下左边数据转换成右边

select 规格,replace(replace(replace(规格,"mm"," "),"x","X"),"*","X") as 统一规格 from [纸箱规格表$]

②如图，将S字段数据进行替换

select S,replace(S,"A","B",0.6) as s1,
replace(S,"A","B",1.4) as s2,
replace(S,"A","B",1.5) as s3,
replace(S,"A","B",1.6) as s4,
replace(S,"A","B",2.5) as s5,
replace(S,"A","B",3.5) as s6 from [字符串$]

replace(S,“A”,“B”,0.5)将返回错误，因为replace函数的第四个参数要求大于0

select S,replace(S,"A","B",1,-1) as s1,   
replace(S,"A","B",1,0) as s2,   
replace(S,"A","B",1,0.6) as s3,    
replace(S,"A","B",1,1.4) as s4,    
replace(S,"A","B",1,1.5) as s5,    
replace(S,"A","B",1,1.6) as s6,    
replace(S,"A","B",1,2.5) as s7,    
replace(S,"A","B",1,3.5) as s8 from [字符串$]

replace(S,“A”,“B”,1,-1)=replace(S,“A”,“B”)
replace(S,“A”,“B”,1,-2)将返回错误，因为count参数最小为-1

补充：关于四舍六入五单双的规则

• 当需要舍弃部分最左一位数字<>5时，遵循四舍五入
• 当需舍弃部分最左一位数字为5时，如果5右边的有效数字非零，则进一位。如果为零，判断5前面数字的奇偶性，奇数进一位，偶数则舍去
  1.492保留两位小数>>>1.49，保留一位>>>1.5
  1.452保留一位小数>>>1.5
  1.55保留一位小数>>>1.6
  1.45保留一位小数>>>1.4
  口诀：四舍六入五考虑，五后非零就进一，五后皆零看奇偶，五前为偶应舍去，五前为奇就进一