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