Sql Server - Filter Field Contents To Numbers Only

How can I copy the value of a field, but only its numbers? I am creating a computed column for fulltext search, and I want to copy the values from my Phone Number fields (which are

Solution 1:

You are going to have to write a user defined function to do this. There are several ways to do this, here is one that I found with some quick Googling.

CREATEFUNCTION dbo.RemoveChars(@Inputvarchar(1000))
RETURNSVARCHAR(1000)
BEGINDECLARE@posINTSET@Pos= PATINDEX('%[^0-9]%',@Input)
  WHILE @Pos>0BEGINSET@Input= STUFF(@Input,@pos,1,'')
    SET@Pos= PATINDEX('%[^0-9]%',@Input)
   ENDRETURN@InputEND

Warning: I wouldn't put this in a WHERE condition on a large table, or in a SELECT that returns millions of rows, but it will work.

Ultimately you are probably better stripping the non-numeric characters out in the UI of your app than in DB code.

Solution 2:

Assuming there's only a couple of non-number characters, a nested replace functions do the trick:

select replace(replace(replace(col1,'-',''),'(',''),')','')
from YourTable

You can check if you caught all characters like:

select col1
from YourTable
where col1 notlike'%[-()0-9]%'

(This example is checking for -, (), and numbers.)

Solution 3:

I'd create a user-defined function that you could use in your select and where criteria, maybe something like this:

DECLARE@positionint, @resultvarchar(50)
SET@position=1SET@result=''

WHILE @position<= DATALENGTH(@input)
    BEGIN
    IF ASCII(SUBSTRING(@input, @position, 1)) BETWEEN48AND57BEGINSET@result=@result+SUBSTRING(@input, @position, 1)
        ENDSET@position=@position+1ENDRETURN@result

Best of luck!

Solution 4:

I realize this is a somewhat older question but there is no need to resort to looping for this. And these days we should try to avoid scalar functions when possible as they are not good for performance. We can leverage an inline table valued function in conjunction with the light support of regular expressions that we have in sql server. This article from Jeff Moden explains this in more detail from the perspective of why IsNumeric does not really work. http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/

The gist of it is this nifty function he put together.

CREATE FUNCTION dbo.IsAllDigits 
/********************************************************************
 Purpose:
 This function will return a 1 if the string parameter contains only 
 numeric digits and will return a 0 in all other cases.  Use it in
 a FROM clause along with CROSS APPLY when used against a table.

 --Jeff Moden
********************************************************************/
--===== Declare the I/O parameters
        (@MyString VARCHAR(8000))
RETURNS TABLE AS
 RETURN (
         SELECT CASE 
                WHEN @MyString NOT LIKE '%[^0-9]%'
                THEN 1
                ELSE 0
                END AS IsAllDigits
        )