SQL Server T-SQL User-Defined Function of the Week

Proper Case a String

Volume 1 Number 47     October 7, 2003

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

He's at it again. Srinivas Sampath has contributed this week's 
UDF, which fixes the case of a string so for titles and names.
It changes all the first characters to upper case and the 
rest of the characters to lower case.

It arrived yesterday.  Just in the nick of time.  I'm putting the
finishing touches on by book: Transact-SQL User-Defined Functions
It'll be published by Wordware in about a month.  Stay tuned
for more about it in future editions.

Before we get to this week's UDF I also have an article alert.
Database Journal has just published my article
Implementing CRUD Operations Using Stored Procedures: Part 1
You'll find it at:
http://www.databasejournal.com/features/mssql/article.php/3082201
Part 2 will be published there, also in about a month.

Now here's this month's CREATE FUNCTION script:
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

CREATE FUNCTION dbo.Proper (

    @tcString VARCHAR(100)
)   RETURNS VARCHAR(100)
AS BEGIN
-- Scratch variables used for processing
DECLARE @outputString VARCHAR(100)
DECLARE @stringLength INT
DECLARE @loopCounter INT
DECLARE @charAtPos VARCHAR(1)
DECLARE @wordStart INT

-- If the incoming string is NULL, return an error
IF (@tcString IS NULL)
	RETURN ('(no string passed)')

-- Initialize the scratch variables
SET @outputString = ''
SET @stringLength = LEN (@tcString)
SET @loopCounter = 1
SET @wordStart = 1

-- Loop over the string
WHILE (@loopCounter <= @stringLength)
BEGIN
	-- Get the single character off the string
	SET @charAtPos = LOWER(SUBSTRING (@tcString, @loopCounter, 1))

	-- If we are the start of a word, uppercase the character
	-- and reset the word indicator
	IF (@wordStart = 1)
	BEGIN
		SET @charAtPos = UPPER (@charAtPos)
		SET @wordStart = 0
	END

	-- If we encounter a white space, indicate that we
	-- are about to start a word
	IF (@charAtPos = ' ')
		SET @wordStart = 1

	-- Form the output string
	SET @outputString = @outputString + @charAtPos

	SET @loopCounter = @loopCounter + 1
END

-- Return the final output
RETURN (@outputString)
END
GO

GRANT EXEC on dbo.Proper to PUBLIC
GO
\-------Stop copying above this line ---------------------------/

To test the UDF, requires only a simple function call with a 
name. Let's try it on the name of one of my favorite professors
from Brown and while we're at it, check on what it does with 
NULL.
/------- Start copying below this line -------------------------\
SELECT dbo.proper ('prof. josiah carberry') as [Proper Case]
     , dbo.proper (null) [What does it do with null]
GO
\-------Stop copying above this line ---------------------------/
(Results)
Proper Case               What does it do with null                                      
------------------------- ---------------------------
Prof. Josiah Carberry     (no string passed)         

(End of results)

Because this function returns a character string result, the
author has made the choice to return the error message as the
return value of the function.  You see that in the second (right)
invocation.

There's room to expand on this UDF.  For names, there are special
cases such as hyphenated names and mixed case names such as 
McIntire.  For titles, such as book titles, connecting words such
"of" or "or" are usually left in lower case.  Maybe I'll tackle 
this in a future issue.

This UDF is based on Srinivas's article which you can find at:
http://www32.brinkster.com/srisamp/sqlArticles/article_28.htm
Thanks for the contribution Srinivas!
 

Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+