By David Wiseman (Administrator)published 30 Sep 2007, modified 02 Mar 2009
My Rating:
Vote
Rating:
Not Rated
Views:46362

Proper Case User Defined Function T-SQL

The Problem

We have UPPERCASE (or lowercase) text in our database and we want to convert it to Proper Case/Title Case for reporting purposes. This involves converting the text into lowercase and converting the first letter of each word into UPPERCASE.
e.g.
Convert this: A LONG TIME AGO IN A GALAXY FAR, FAR AWAY
To this: A Long Time Ago In A Galaxy Far, Far Away
 
Unfortunately, the problem is not always this simple; sometimes you might want to make exceptions. In the text above you might decide that "in" and "a" are not important enough to be capitalised. 
e.g. A Long Time Ago in a Galaxy Far, Far Away
You might have other exceptions such RAM, TFT, CPU, ASCII. Some exceptions might also be of mixed case, such as DoS, QoS, WiseSoft, TomTom. Creating a Proper Case function is not an exact science, and this is probably why it wasn't included as a system function in T-SQL. 

The Solution

A solution is required that can convert the first letter of each word into UPPERCASE and the rest of the word into lowercase. In addition it needs to be able to handle exceptions such as those mentioned previously. The function will allow a user to specify a list of exceptions, by inputting them into them into one of the functions parameters and also handle a few expected grammatical exceptions. 
In some situations, you might want to assume that words of a specified length should by UPPERCASE, so the function will have 3 parameters in total:
@Value - The text you want to convert
@Exceptions – A list of exceptions. Exceptions will be printed exactly as specified. Specify NULL if you have no exceptions.
@UCASEWordLength – Words shorter than the value specified will automatically be printed in UPPERCASE. Specify NULL if you don’t want to use this feature. 
The function will convert the text to lower case, and then parse the text character by character. The first character is converted to UPPERCASE. Each character is appended to a “word” variable until a white space/punctuation character is reached, marking the end of the word. At this point the word will be compared to the list of user exceptions and the case converted if required. Some common grammatical exceptions will also be checked – e.g. WON'T will be converted to Won't with these exceptions instead of Won'T. The function will display names such as O'Donnell correctly. Once the word has been cased correctly with exceptions accounted for, it is appended to a variable used to store the return value along with the white space/punctuation character following it. The process is repeated until all of the text has been converted. 

T-SQL Code

CREATE FUNCTION [dbo].[fProperCase](@Value varchar(8000), @Exceptions varchar(8000),@UCASEWordLength tinyint)
returns varchar(8000)
as
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function Purpose: To convert text to Proper Case.
Created By:             David Wiseman
Website:                http://www.wisesoft.co.uk
Created:                2005-10-03
Updated:                2006-06-22
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INPUTS:
 
@Value :                This is the text to be converted to Proper Case
@Exceptions:            A list of exceptions to the default Proper Case rules. e.g. |RAM|CPU|HDD|TFT|
                              Without exception list they would display as Ram, Cpu, Hdd and Tft
                              Note the use of the Pipe "|" symbol to separate exceptions.
                              (You can change the @sep variable to something else if you prefer)
@UCASEWordLength: You can specify that words less than a certain length are automatically displayed in UPPERCASE
 
USAGE1:
 
Convert text to ProperCase, without any exceptions
 
select dbo.fProperCase('THIS FUNCTION WAS CREATED BY DAVID WISEMAN',null,null)
>> This Function Was Created By David Wiseman
 
USAGE2:
 
Convert text to Proper Case, with exception for WiseSoft
 
select dbo.fProperCase('THIS FUNCTION WAS CREATED BY DAVID WISEMAN @ WISESOFT','|WiseSoft|',null)
>> This Function Was Created By David Wiseman @ WiseSoft
 
USAGE3:
 
Convert text to Proper Case and default words less than 3 chars to UPPERCASE
 
select dbo.fProperCase('SIMPSON, HJ',null,3)
>> Simpson, HJ
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
begin
      declare @sep char(1) -- Seperator character for exceptions
      declare @i int -- counter
      declare @ProperCaseText varchar(5000) -- Used to build our Proper Case string for Function return
      declare @Word varchar(1000) -- Temporary storage for each word
      declare @IsWhiteSpace as bit -- Used to indicate whitespace character/start of new word
      declare @c char(1) -- Temp storage location for each character
 
      set @Word = ''
      set @i = 1
      set @IsWhiteSpace = 1
      set @ProperCaseText = ''
      set @sep = '|'
 
      -- Set default UPPERCASEWord Length
      if @UCASEWordLength is null set @UCASEWordLength = 1
      -- Convert user input to lower case (This function will UPPERCASE words as required)
      set @Value = LOWER(@Value)
 
      -- Loop while counter is less than text lenth (for each character in...)
      while (@i <= len(@Value)+1)
      begin
 
            -- Get the current character
            set @c = SUBSTRING(@Value,@i,1)
 
            -- If start of new word, UPPERCASE character
            if @IsWhiteSpace = 1 set @c = UPPER(@c)
 
            -- Check if character is white space/symbol (using ascii values)
            set @IsWhiteSpace = case when (ASCII(@c) between 48 and 58) then 0
                                          when (ASCII(@c) between 64 and 90) then 0
                                          when (ASCII(@c) between 96 and 123) then 0
                                          else 1 end
 
            if @IsWhiteSpace = 0
            begin
                  -- Append character to temp @Word variable if not whitespace
                  set @Word = @Word + @c
            end
            else
            begin
                  -- Character is white space/punctuation/symbol which marks the end of our current word.
                  -- If word length is less than or equal to the UPPERCASE word length, convert to upper case.
                  -- e.g. you can specify a @UCASEWordLength of 3 to automatically UPPERCASE all 3 letter words.
                  set @Word = case when len(@Word) <= @UCASEWordLength then UPPER(@Word) else @Word end
 
                  -- Check word against user exceptions list. If exception is found, use the case specified in the exception.
                  -- e.g. WiseSoft, RAM, CPU.
                  -- If word isn't in user exceptions list, check for "known" exceptions.
                  set @Word = case when charindex(@sep + @Word + @sep,@exceptions collate Latin1_General_CI_AS) > 0
                                    then substring(@exceptions,charindex(@sep + @Word + @sep,@exceptions collate Latin1_General_CI_AS)+1,len(@Word))
                                    when @Word = 's' and substring(@Value,@i-2,1) = '''' then 's' -- e.g. Who's
                                    when @Word = 't' and substring(@Value,@i-2,1) = '''' then 't' -- e.g. Don't
                                    when @Word = 'm' and substring(@Value,@i-2,1) = '''' then 'm' -- e.g. I'm
                                    when @Word = 'll' and substring(@Value,@i-3,1) = '''' then 'll' -- e.g. He'll
                                    when @Word = 've' and substring(@Value,@i-3,1) = '''' then 've' -- e.g. Could've
                                    else @Word end
 
                  -- Append the word to the @ProperCaseText along with the whitespace character
                  set @ProperCaseText = @ProperCaseText + @Word + @c
                  -- Reset the Temp @Word variable, ready for a new word
                  set @Word = ''
            end
            -- Increment the counter
            set @i = @i + 1
      end
      return @ProperCaseText
end






Article Links