Extracting Numeric values from a varchar field
-
I'm trying to standardize the phone number format for some historical records. The phone numbers have been entered in different formats in the past. I would like to extract just the numerical characters from the old phone numbers so I can reformat them into an xxx.xxx.xxxx format. I've looked into using the Replace and Substring functions, but I was hoping an easier solution existed.
-
I'm trying to standardize the phone number format for some historical records. The phone numbers have been entered in different formats in the past. I would like to extract just the numerical characters from the old phone numbers so I can reformat them into an xxx.xxx.xxxx format. I've looked into using the Replace and Substring functions, but I was hoping an easier solution existed.
I found a solution using code provided on this site: http://www.vsj.co.uk/articles/display.asp?id=540[^] Here is a Scalar-valued SQL Function to do the job:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION RemoveNonNumerics ( -- Add the parameters for the function here @sInputValue varchar(24) ) RETURNS varchar(24) AS BEGIN -- Declare the return variable here DECLARE @Result varchar(24) -- Add the T-SQL statements to compute the return value here SELECT @Result = @sInputValue -- Remove non-numeric characters declare @i INT select @i = 25 while @i > 0 begin SELECT @i = patindex('%[^0-9]%',@Result) SELECT @Result = replace(@Result,substring(@Result,@i,1),'') end -- Return the result of the function RETURN @Result END GO