SQL Server - Filter field contents to numbers only

Question

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!

Answer 1

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

Answer 2

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.

Answer 3

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!