It amazes me how many people that write Transact-SQL scripts seem to forget that they can create functions in their SQL database to take care of repetitive, mundane tasks.

For example, you often find a GENDER field in your table that stores an M for male, F for Female and maybe even an O for other and U for unknown. Now what if you have a bunch of reports that you need the full gender name printed on. Create a SQL funtion one time and from then on, it's easy.

Here's one way to write the function:

create function fnExpandGender
   (@inGender varchar(50))
   returns varchar(50) 
as 
begin
   set @inGender = case upper(substring(ltrim(rtrim(@inGender)),1,1))
      when 'M' then 'Male'
      when 'F' then 'Female'
      when 'O' then 'Other'
      else 'Unknown' end
   return (@inGender)
end

Now anytime you need the full gender name, just select it by calling the function as in:

select name, address, dbo.fnExpandGender(gender), phone from employees

Note the dbo. before your function name.

Functions come in really handy when you're working with a poorly built database that has embedded codes, such as Marital Status.

create function fnExpandMaritalStatus
   (@inMS varchar(50))
   returns varchar(50) 
as 
begin 
   set @inMS = case upper(substring(ltrim(rtrim(@inMS)),1,1))
      when 'M' then 'Married'
      when 'S' then 'Single'
      when 'D' then 'Divorced'
      when 'W' then 'Widowed'
      when 'P' then 'Separated'
      when 'C' then 'Common Law'
      when 'O' then 'Other'
      else 'Unknown' end
   return (@inMS)
end 

Or maybe your database tracks a person's race in a single field. Since many people are more than one race, the field consists of one to several codes, each seperated by a semi-colan, such as A;W which you want to read as Asian/White. (this example comes directly from a government form I had to deal with)

create function fnExpandRace
   (@inRace varchar(100))
   returns varchar(5000) 
as 
begin 
   declare @Out varchar(5000)
   set @Out = ''
   declare @nXX int
   set @nXX = 1
   set @inRace = ltrim(rtrim(@inRace))

   while @nXX < datalength(@inRace)+1
      begin
         set @Out = @Out + case upper(substring(@inRace,@nXX,1))
            when ';' then ''
            when 'A' then 'Asian/'
            when 'B' then 'Black/African American/'
            when 'M' then 'Alaskan Native/'
            when 'N' then 'Native American/American Indian/'
            when 'P' then 'Native Hawaiian/Other Pacific Islander/'
            when 'W' then 'White/'
            when 'U' then 'Unknown/'
            else 'Invalid Code: ' + upper(substring(@inRace,@nXX,1)) + '/'
            end
         set @nXX = @nXX + 1
      end
   if datalength(@Out) > 0
   set @Out = substring(@Out,1,datalength(@Out)-1)
   return (@Out)
end 

Of course, you can use functions for other things too, such as converting military time:

CREATE function fnMilitary2RegularTime
   (@inMilitaryTime varchar(9))
   returns varchar(9) 
as 
begin 
   declare 
      @nXX int,
      @cIn varchar(50),
      @cOut varchar(50),
      @cAMPM varchar(50)

   set @cIn = ltrim(rtrim(@inMilitaryTime))

   if @cIn like '%:%'
      set @cIn=substring(@cIn,1,charindex(':',@cIn)-1)+substring(@cIn,charindex(':',@cIn)+1,99)

   while datalength(@cIn) < 4
      begin
         set @cIn = '0' + @cIn
      end

   set @nXX = convert(tinyint,substring(@cIn,1,2))

   if @nXX > 12
      begin
         set @cAMPM = ' pm'
         set @nXX = @nXX - 12
      end
   else
      if @nXX = 12
         set @cAMPM = ' pm'
      else
         if @nXX = 0
            begin
               set @cAMPM = ' am'
               set @nXX = 12
            end
         else
            set @cAMPM = ' am'

   set @cOut = ltrim(rtrim(convert(varchar,@nXX))) + ':' + substring(@cIn,3,2) + @cAMPM

   return (ltrim(rtrim(@cOut))) 
end 

or replacing all carriage returns, line feeds and form feeds with a space:

create function fnStripCRLF
   (@inText varchar(5000))
   returns varchar(5000) 
as 
begin 
   declare @nXX int
   set @nXX = 1

   while @nXX < datalength(@inText)+1
      begin
         if substring(@inText,@nXX,1) = char(10) 
            or substring(@inText,@nXX,1) = char(12) 
            or substring(@inText,@nXX,1) = char(13)
               set @inText = rtrim(substring(@inText,1,@nXX-1))
                  + ' ' + ltrim(substring(@inText,@nXX+1,5000))
         set @nXX = @nXX + 1
      end
   return (ltrim(rtrim(@inText))) 
end

or removing all spaces:

create function fnStripSpaces
   (@inText varchar(5000))
   returns varchar(5000) 
as 
begin 
   set @inText = ltrim(rtrim(@inText))

   while @inText like '% %'
      begin
         set @inText = substring(@inText,1,charindex(' ',@intext)-1)
            + substring(@inText,charindex(' ',@intext)+1,5000)
      end
   return (@inText) 
end

or even replacing any funny characters, and you get to decide what's legitimate, with a space:

create function fnStripOddCharacters
   (@inText varchar(5000))
   returns varchar(5000) 
as 
begin 
   declare @nXX int
   set @nXX = 1

   while @nXX < datalength(@inText)+1
      begin
         -- Add legitimate characters to the string below
         if 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 -'
            not like '%' + substring(@inText,@nXX,1) + '%'
               set @inText = substring(@inText,1,@nXX-1) + ' ' + substring(@inText,@nXX+1,5000)
         set @nXX = @nXX + 1
      end
   return (ltrim(rtrim(@inText))) 
end 

These are simple examples to get you thinking. I hope they help. Enjoy!