FormatSSN.sql

--SET ANSI_NULLS ON
--GO

--SET QUOTED_IDENTIFIER ON
--GO

--IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = 
--OBJECT_ID(N'[dbo].[FormatSSN]') AND TYPE IN (N'FN', N'IF', N'TF', N'FS', N'FT')) 
--DROP FUNCTION [dbo].[FormatSSN]
--GO

CREATE FUNCTION [dbo].[FormatSSN] (@UnformattedSSN varchar(50))  
RETURNS INT 
AS 
BEGIN 
   DECLARE @FormattedSSN varchar(12) = REPLACE(REPLACE(@UnformattedSSN,'-',''),' ','')

	SET @FormattedSSN = CASE 
							WHEN ISNUMERIC(@FormattedSSN) !=1 THEN NULL
							WHEN LEN(@FormattedSSN) <> 9 THEN NULL
							ELSE @FormattedSSN
						END
	RETURN @FormattedSSN
END