Format the ZIP code

Posted by VIJI Friday, July 3, 2009

About FuncZipCode SQL user defined function

/*This function returns a formatted 10 digit zip code xxxxx-xxxx */
/*This function attaches -0000 if gthe input zip code is 5 digits */
/*This function returns the zip code if the length is 10 digits */


CREATE FUNCTION dbo.FuncZipCode(@Zip varChar(50)) RETURNS varChar(50)
AS
BEGIN
Declare @varZipCode as varChar(50)
if @Zip is null Begin
Return(Null)
End
Else Begin

if len(@Zip) = 5 Begin
Set @varZipCode = RTRIM(@Zip) + '-0000'
Return(@varZipCode)
End
Else Begin
if len(@Zip) = 10 Begin
Set @varZipCode = RTRIM(@Zip)
Return (@varZipCode)
End
End
End
Return (@Zip)
End



Usage

dbo.FuncZipCode('Zip Code')

/*
Example:
dbo.FuncZipCode('12345') will return 12345-0000

0 Responses to Format the ZIP code

Post a Comment