Remove all non numeric characters in sql SELECT

Question

I want to remove all non-numeric characters when I call the query in SQL. I have a 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 to do it with query (select). I tried so but this doesn't work

select substring(AdrTelefon1, PatIndex('%[^0-9]%', AdrTelefon1), 2000) from test

EDIT I have it! Select query to remove non-numeric characters

It does not work correctly

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 after this query, I have 04532 BUT I NEED 0453297

Answer 1

@Emma W.

I agree with the others... you actually should use a function for this. Here's a very high-performance function that will work for 2008 and above. It includes full documentation and usage examples.

As a bit of a sidebar, any function that contains the word BEGIN is either a slow, performance hogging, scalar function or mTFV (multi-statement Table Valued Function). Most savvy DBAs won't allow either but may not know the difference between those two and an iTVF (inline Table Valued Function), like the one below.

 CREATE OR ALTER FUNCTION [dbo].[DigitsOnly]
/**********************************************************************************************************************
 Purpose:
 Given a VARCHAR(8000) or less string, return only the numeric digits from the string.
 Programmer's Notes:
 1. This is an iTVF (Inline Table Valued Function) that will be used as an iSF (Inline Scalar Function) in that it
    returns a single value in the returned table and should normally be used in the FROM clause as with any other iTVF.
 2. The main performance enhancement is using a WHERE clause calculation to prevent the relatively expensive XML PATH
    concatentation of empty strings normally determined by a CASE statement in the XML "loop".
 3. Another performance enhancement is not making this function a generic function that could handle a pattern. That 
    allows us to use all integer math to do the comparison using the high speed ASCII function convert characters to
    their numeric equivalent.  ASCII characters 48 through 57 are the digit characters of 0 through 9 in most languages.
 4. Last but not least, added another of Eirikur's later optimizations using 0x7FFF which he says is a "simple trick to
    shift all the negative values to the top of the range so a single operator can be applied, which is a lot less 
    expensive than using between.
-----------------------------------------------------------------------------------------------------------------------
 Kudos:
 1. Hats off to Eirikur Eiriksson for the ASCII conversion idea and for the reminders that dedicated functions will 
    always be faster than generic functions and that integer math beats the tar out of character comparisons that use 
    LIKE or PATINDEX.
 2. Hats off to all of the good people that submitted and tested their code on the following thread. It's this type of
    participation and interest that makes code better.  You've just gotta love this commmunity.
    http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx#bm1629360
-----------------------------------------------------------------------------------------------------------------------
 Usage Example:
--===== CROSS APPLY example
 SELECT ca.DigitsOnly
   FROM dbo.SomeTable st
  CROSS APPLY dbo.DigitsOnly(st.SomeVarcharCol) ca
;
-----------------------------------------------------------------------------------------------------------------------
 Test Harness:
--===== Create the 1 Million row test table
   DROP TABLE IF EXISTS #TestTable
;
 SELECT TOP 1000000
        Txt = ISNULL(CONVERT(VARCHAR(36),NEWID()),'')
   INTO #TestTable
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;
  ALTER TABLE #TestTable
    ADD PRIMARY KEY CLUSTERED (Txt)
;
GO
--===== CROSS APPLY example.
     -- This takes ~ 1 second to execute.
   DROP TABLE IF EXISTS #Results;
 SELECT tt.Txt, ca.DigitsOnly
   INTO #Results
   FROM #TestTable tt
  CROSS APPLY dbo.DigitsOnly(Txt) ca
;
GO
--===== Return the results for manual verification.
 SELECT * FROM #Results
;
-----------------------------------------------------------------------------------------------------------------------
 Revision History:
 Rev 00 - 28 Oct 2014 - Eirikur Eiriksson
        - Initial creation and unit/performance tests.
 Rev 01 - 29 Oct 2014 - Jeff Moden
        - Performance enhancement and unit/performance tests.
 Rev 02 - 30 Oct 2014 - Eirikur Eiriksson
        - Additional Performance enhancement
 Rev 03 - 01 Sep 2014 - Jeff Moden
        - Formalize the code and add the documenation that appears in the flower box of this code.
***********************************************************************************************************************/
--======= Declare the I/O for this function
          (@pString VARCHAR(8000))
  RETURNS TABLE WITH SCHEMABINDING AS 
   RETURN WITH
    E1(N) AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS E0(N))
,Tally(N) AS (SELECT TOP (LEN(@pString)) (ROW_NUMBER() OVER (ORDER BY (SELECT 1))) FROM E1 a,E1 b,E1 c,E1 d)
   SELECT DigitsOnly =
          (
           SELECT SUBSTRING(@pString,N,1)
             FROM Tally
            WHERE ((ASCII(SUBSTRING(@pString,N,1)) - 48) & 0x7FFF) < 10
              FOR XML PATH('')
          )
;
GO

If you're really up against a wall and cannot use a function of any type because of "Rules" that have no exceptions (a really bad idea), then post back and we can show you how to convert it into inline code with a little help from you.

Whatever you do, don't use a WHILE loop for this task... it'll kill you performance and resource usage wise.

Answer 2

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