删除sql SELECT中的所有非数字字符 [英] Remove all non numeric characters in sql SELECT
问题描述
我想在 sql 中调用查询时删除所有非数字字符.我有函数,在函数中我这样做:
I want remove all non-numeric characters when I call the query in sql. I have function and in function I do it so:
Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^0-9]%'
While PatIndex(@KeepValues, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
但现在我想用查询(选择)来做.我试过了,但这不起作用
But now I want do it with query (select). I tryed so but this don't work
select substring(AdrTelefon1, PatIndex('%[^0-9]%', AdrTelefon1), 2000) from test
编辑我有!T-SQL 选择查询以删除非数字字符
它不能正常工作
SELECT LEFT(SUBSTRING(AdrTelefon1, PATINDEX('%[0-9]%', AdrTelefon1), 8000),
PATINDEX('%[^0-9]%', SUBSTRING(AdrTelefon1, PATINDEX('%[0-9]%', AdrTelefon1), 8000) + 'X') -1) from test
我有 04532/97,在此查询之后我有 04532 但我需要 0453297
I have 04532/97 and afte this query I have 04532 BUT I NEED 0453297
推荐答案
前段时间我用下面的函数解决了这个问题
Some time ago I solved that problem using the below function
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 替换.
Note it ill also consider characters °,¹,²,³ numeric and replace then with 0,1,2,3.
我将它放在一个函数中,以便在我需要一次修复多个表中的多个列的场景中方便地重用它.
I put it in a function to readly reuse it in my scenario I needed to fix many columns in many tables at once.
update t
set t.myColumn = dbo.[fnrReplacetor](tempdb.myColumn)
from test t
where tempdb.myColumn is not null
或者只是
select dbo.[fnrReplacetor](tempdb.myColumn) as [Only Digits]
from test t
where tempdb.myColumn is not null
Obs:这不是最致命的方法,而是彻底的方法.
Obs: this is not the fatest way but a thorough one.
编辑
非 UDF 解决方案必须使用 REPLACE
但由于正则表达式在 SQL 中不是那么好,你可以结束做一些讨厌的事情,如下例:
A non UDF solution must be use REPLACE
but since regex is not that great in SQL you can end doing something nasty like the below example:
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
这篇关于删除sql SELECT中的所有非数字字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!