Propercase User Defined Function T-SQL

Page 1 of 4

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 ProperCase/TitleCase 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

CREATEFUNCTION [dbo].[fProperCase](@Value varchar(8000), @Exceptions varchar(8000),@UCASEWordLength tinyint)

returnsvarchar(8000)

as

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Function Purpose:To convert text to Proper Case.

Created By:David Wiseman

Website:

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 asbit-- 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 isnullset @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 =casewhen(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 =casewhenlen(@Word)<= @UCASEWordLength thenUPPER(@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 =casewhencharindex(@sep + @Word + @sep,@exceptions collate Latin1_General_CI_AS) 0

thensubstring(@exceptions,charindex(@sep + @Word + @sep,@exceptions collate Latin1_General_CI_AS)+1,len(@Word))

when @Word ='s'andsubstring(@Value,@i-2,1)=''''then's'-- e.g. Who's

when @Word ='t'andsubstring(@Value,@i-2,1)=''''then't'-- e.g. Don't

when @Word ='m'andsubstring(@Value,@i-2,1)=''''then'm'-- e.g. I'm

when @Word ='ll'andsubstring(@Value,@i-3,1)=''''then'll'-- e.g. He'll

when @Word ='ve'andsubstring(@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

Created by David Wiseman