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 varchar) into it, but not with their formatting - numbers only. What is the command that would do this in my computed column formula?

Thank you!

---

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.

CREATE FUNCTION dbo.RemoveChars(@Input varchar(1000))
RETURNS VARCHAR(1000)
BEGIN
  DECLARE @pos INT
  SET @Pos = PATINDEX('%[^0-9]%',@Input)
  WHILE @Pos > 0
   BEGIN
    SET @Input = STUFF(@Input,@pos,1,'')
    SET @Pos = PATINDEX('%[^0-9]%',@Input)
   END
  RETURN @Input
END

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.

---

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 not like '%[-()0-9]%'

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

---

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

DECLARE @position int, @result varchar(50)
SET @position = 1
SET @result = ''

WHILE @position <= DATALENGTH(@input)
    BEGIN
    IF ASCII(SUBSTRING(@input, @position, 1)) BETWEEN 48 AND 57
        BEGIN
        SET @result = @result + SUBSTRING(@input, @position, 1)
        END
    SET @position = @position + 1
    END

RETURN @result

Best of luck!

---

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
        )