Alex Alex - 1 year ago 75
SQL Question

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!

Answer Source

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))
  SET @Pos = PATINDEX('%[^0-9]%',@Input)
  WHILE @Pos > 0
    SET @Input = STUFF(@Input,@pos,1,'')
    SET @Pos = PATINDEX('%[^0-9]%',@Input)
  RETURN @Input

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.