前段时间我使用下面的函数解决了这个问题
create function dbo.[fnrReplacetor](@strtext varchar(2000))
returns varchar(2000)
as
begin
declare @i int = 32, @rplc varchar(1) = '';
while @i < 256
begin
if (@i < 48 or @i > 57) and CHARINDEX(char(@i),@strtext) > 0
begin
--° #176 ~ 0 --¹ #185 ~ 1 --² #178 ~ 2 --³ #179 ~ 3
set @rplc = case @i
when 176 then '0'
when 185 then '1'
when 178 then '2'
when 179 then '3'
else '' end;
set @strtext = REPLACE(@strtext,CHAR(@i),@rplc);
end
set @i = @i + 1;
end
return @strtext;
end
GO
select dbo.[fnrReplacetor]('12345/97')
请注意,我也会考虑字符°,¹,²,³数字,然后替换为 0,1,2,3。
我将其放入一个函数中,以便在我需要同时修复许多表中的许多列的场景中轻松重用它。
update t
set t.myColumn = dbo.[fnrReplacetor](tempdb.myColumn)
from test t
where tempdb.myColumn is not null
or just
select dbo.[fnrReplacetor](tempdb.myColumn) as [Only Digits]
from test t
where tempdb.myColumn is not null
Obs:这不是最快的方法,而是彻底的方法。
Edit
必须使用非 UDF 解决方案REPLACE
但由于正则表达式在 SQL 中并不是那么好,你可以结束做一些令人讨厌的事情,如下例所示:
declare @test as table (myColumn varchar(50))
insert into @test values ('123/45'),('123-4.5')
Select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(myColumn,'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'q',''),'r',''),'s',''),'t',''),'u',''),'v',''),'w',''),'x',''),'y',''),'z',''),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'Q',''),'R',''),'S',''),'T',''),'U',''),'V',''),'W',''),'X',''),'Y',''),'Z',''),'.',''),'-',''),'/','')
from @test