Remove all non numeric characters in sql SELECT

Question

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

EDIT I have it! T-SQL select query to remove non-numeric characters

It does not work properly

SELECT LEFT(SUBSTRING(AdrTelefon1, PATINDEX('%[0-9]%', AdrTelefon1), 8000),
       PATINDEX('%[^0-9]%', SUBSTRING(AdrTelefon1, PATINDEX('%[0-9]%', AdrTelefon1), 8000) + 'X') -1) from test

I have 04532/97 and afte this query I have 04532 BUT I NEED 0453297

SQL Server does not have any built-in regex replacement support. You might have to use a UDF if you really need to do this. — Tim Biegeleisen, Aug 23 '17 at 11:35
There are a [bunch of workarounds](https://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters) of various types. — Tim Biegeleisen, Aug 23 '17 at 11:36
have a look at this https://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server — RealCheeseLord, Aug 23 '17 at 11:40

jean · Accepted Answer · 2020-07-17T12:22:04.740

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')

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

or just

select dbo.[fnrReplacetor](tempdb.myColumn) as [Only Digits]
 from test t
where tempdb.myColumn is not null

Obs: this is not the fatest way but a thorough one.

Edit

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

@EmmaW. I disagree you do need a UDF for a most complete solution but you can see my edit with a very ugly non UDF solution — jean, Aug 23 '17 at 13:42

Remove all non numeric characters in sql SELECT

1 Answers1

Linked