name department email
john 技术部 [email protected]
tom 人力资源部 [email protected]
jim 财务部 [email protected]
要将EMAIL字段中所有的非数字给过滤掉
john 技术部 [email protected]
tom 人力资源部 [email protected]
jim 财务部 [email protected]
要将EMAIL字段中所有的非数字给过滤掉
returns varchar(400)
as
begin
while patindex('%[^0-9]%',@str)>0
begin
set @str = stuff( @str, patindex('%[^0-9]%',@str) ,1 ,'')
end
return @str
end
go
If object_id('ta') is not null
Drop table ta
Go
Create table ta(name varchar(4),department varchar(20),email varchar(20))
Go
Insert into ta
select 'john','技术部','[email protected]' union all
select 'tom','人力资源部','[email protected]' union all
select 'jim','财务部','[email protected]'
Go
--Start
create function f_str(@str varchar(400))
returns varchar(400)
as
begin
while patindex('%[^0-9]%',@str)>0
begin
set @str = stuff( @str, patindex('%[^0-9]%',@str) ,1 ,'')
end
return @str
end
goupdate ta
set email = dbo.f_str(email)select * from tadrop function f_str
--Result:
/*
name department email
---- -------------------- --------------------
john 技术部 126
tom 人力资源部 126
jim 财务部 126(所影响的行数为 3 行)*/
--End
returns varchar(50)
as
begin
declare @i int
set @i=1
while patindex('%[^1-9]%',@s) > 0 and @i <=len(@s)
begin
if substring(@s,@i,1) not like '%[1-9]%'
set @s=stuff(@s,@i,1,'')
else
set @i=@i+1
end
return @s
end
go create table a
(
email varchar(50)
)
insert into a values('[email protected]')
insert into a values('[email protected]')
insert into a values('[email protected]')update a
set email = dbo.get_china(email) select * from a
结果
--------------------------------------------------
126
126
126(3 row(s) affected)