September 27, 2010 at 11:33 am
Hi,
I have to create a UDF for validating phone numbers from a staging table to another table with the following pattern 713-666-7777
If the Staging table contains Phone numbers of the following format given below then,
Staging Table Main Table
7132998206 --> 713-299-8206
(713)299-8206 --> 713-299-8206
(291)-262-2223 --> 291-262-2233
713/2998206 --> 713-299-8206
555-666-9999 --> do nothing . Insert as it is.
September 27, 2010 at 12:29 pm
Basically you just want to strip all non-numeric values, and add two hyphens.
There are a lot of variations on the function to remove non-numerics. The one I use is from: http://ssmithdev.com/2008/12/10/transact-sql-stripnonnumeric-function/
Here is the modification to add dashes, with some validation examples at the end:
DROP FUNCTION dbo.PhoneValidate
GO
CREATE FUNCTION dbo.PhoneValidate(@value AS VARCHAR(MAX)) RETURNS VARCHAR(12) AS
BEGIN
DECLARE @len AS INT
DECLARE @pattern AS VARCHAR(5)
DECLARE @result AS VARCHAR(12)
SET @len = LEN(@Value)
SET @pattern = '[0-9]'
SET @result = ''
WHILE @len> 0
BEGIN
SET @result = @result + CASE WHEN SUBSTRING(@value,@len,1) LIKE @pattern THEN SUBSTRING(@value,@len,1) ELSE '' END
SET @len = @len - 1
END
SET @result = reverse(@result)
SET @result = SUBSTRING(@result,1,3) + '-' + SUBSTRING(@result,4,3) + '-' + SUBSTRING(@result,7,4)
RETURN @RESULT
END
GO
SELECT dbo.PhoneValidate('(123) 855-5984')
UNION ALL
SELECT dbo.PhoneValidate('1234567890')
UNION ALL
SELECT dbo.PhoneValidate('098-765-4321')
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply