Whether data is entered into your SQL Server system from a Web site, internal applications, or through files, the presentation of the data is always a concern. In this tutorial, I will create a function that will be useful in terms of updating data or just displaying data to end users in a pleasing manner. (This example works for SQL Server 2000 and above.)
When data is received and processed into source systems, it is not always the most pleasing to the eye. Such formats could include all uppercase data, all lowercase data, data without punctuation when necessary, and many of types of related scenarios.
Most of the time, the presentation of the data isn't a huge deal — just pull from the database and present whatever you have. However, other times it is necessary to make the data a little more presentable, such as on a Web page.
There are several ways to present the data; one would be through the Web code itself, while another would be to format it in the database. This example shows a simple function that you could use to return data to the end user or to update the data in the database so the data is stored in a permanent way.
Below is the script to create the function named udf_CorrectCasing. The function will accept a character string of any length and will return the same string, but will every first letter of the words in the string capitalized, with the rest of the words in the string as lowercase. So, passing in a character string of "fort wAyne" will return "Fort Wayne".
CREATE FUNCTION udf_CorrectCasingThe logic behind the function
DECLARE @Length INT, @Increment INT, @NewString VARCHAR(MAX)
DECLARE @CurrentCharacter CHAR(1), @PreviousCharacter CHAR(1)
SET @Length = LEN(LTRIM(RTRIM(@string)))
SET @Increment = @Length - 1
SET @NewString = ''
SET @PreviousCharacter = ''
SET @String = LOWER(@String)
WHILE @Increment >= 0
SET @CurrentCharacter = SUBSTRING(@String, (@Length-@Increment), 1)
SET @NewString = @NewString + CASE WHEN @PreviousCharacter = '' THEN
UPPER(@CurrentCharacter) ELSE @CurrentCharacter END
SET @PreviousCharacter = @CurrentCharacter
SET @Increment = @Increment - 1
I am going to need to loop through all of the characters of the string passed into the function to see if I need to change the casing. This is going to make the function run relatively slow but, hopefully, not slow enough to notice. There are other ways to do this (e.g., such as using regular expressions in the CLR), but for our purposes, the use of the function is fine.
First, I determine the length of the string. Next, I set a few helper variables that I will use later in the function. Then, I set all of the characters to lowercase. Inside the loop, I build a new string by adding the newest character and determining the case of the character by checking to see if the previous character is a space. If it is a space, the next character will be capitalized. Once I have looped through all of the characters in the string, I return the new string.
Here is an example of how to call the udf_CorrectCasing function:
SELECT dbo.udf_CorrectCasing(Address) FROM Customers;
Here is an example of how to call our new function to update the Address column in a table named Customers:
UPDATE Customers SET Address = dbo.udf_CorrectCasing(Address)
Change as needed
The function I outline above is a simple function that will uppercase the first letter of every word that you pass into the function. While this is useful, it probably won't encompass all of your needs for data beautification. You may need any name prefix such as DR, MR, or MRS to always be capitalized or end with a period.
You should experiment with this function to get the results that you need. If you mess up, you can always copy this script again and start from scratch.
Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at firstname.lastname@example.org.
————————————————————————————————————————————-Get database tips in your inbox
TechRepublic's free Database Management newsletter, delivered each Tuesday, contains hands-on SQL Server and Oracle tips and resources. Automatically subscribe today!
Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.