a.addEr+'|' 这个是容错处理的,防止你的 addEr 中没有 | 时,处理不正确. 如果你的字段中确保每条记录都有|,则直接用:select A.* from A,B where stuff(a.addEr,1,charindex('|',a.addEr),'') = B.userID
select A.* from A,B where CAST(substr(a.addEr,5,len(a.addEr)-4)) as INT) = B.userID
select A.* from A,B where left(reverse(a.addEr),charindex('|',a.addEr),0)-1)=B.userID
select A.* from A,B where reverse(left(reverse(a.addEr),charindex('|',a.addEr),0)-1))=B.userID
那如果字段值是这种组合呢?char|int|date取当中的 int 数字,该怎么弄? 急用,谢谢!
这样对不对?select A.* from A,B where LEFT(STUFF(A.addEr,1,CHARINDEX("|",A.addEr),""),CHARINDEX("|",STUFF(A.addEr,1,CHARINDEX("|",A.addEr),""))-1) =B.userID
select A.* from A,B where substring(A.addEr,charindex('|',A.addEr)+1,charindex('|',A.addEr,charindex('|',A.addEr)+1)-charindex('|',A.addEr))=B.userID
--少减了1select A.* from A,B where substring(A.addEr,charindex('|',A.addEr)+1,charindex('|',A.addEr,charindex('|',A.addEr)+1)-charindex('|',A.addEr)-1)=B.userID
如果你的字段中确保每条记录都有|,则直接用:select A.* from A,B where stuff(a.addEr,1,charindex('|',a.addEr),'') = B.userID
急用,谢谢!
where substring(A.addEr,charindex('|',A.addEr)+1,charindex('|',A.addEr,charindex('|',A.addEr)+1)-charindex('|',A.addEr))=B.userID
where substring(A.addEr,charindex('|',A.addEr)+1,charindex('|',A.addEr,charindex('|',A.addEr)+1)-charindex('|',A.addEr)-1)=B.userID