Free Software, Source Code, and Best-Practice Documentation Index from your
Cleveland Software, Database, Web Design, Consulting, and SQL / Delphi Experts

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udfConvertUnformattedSSNToFormattedSSN]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udfConvertUnformattedSSNToFormattedSSN]
GO

-- **********************************************************************************************************************
-- Convert unformatted Social-Security Numbers (i.e., no embedded hyphens) to standard "xxx-xx-xxxx" SSN display format.
-- Example: 
--	PRINT dbo.udfConvertUnformattedSSNToFormattedSSN('123456789') --returns '123-45-6789' 
-- ***********************************************************************************************************************
CREATE FUNCTION udfConvertUnformattedSSNToFormattedSSN
	(@cSSN	CHAR(9))
	RETURNS	CHAR(11)
AS
BEGIN
     RETURN SUBSTRING(@cSSN, 1, 3) + '-' + SUBSTRING(@cSSN, 4, 2) + '-' + SUBSTRING(@cSSN, 6,4) 
END