Proper Case Title Case for Last Names

,

This might not be the fastes script, in the world but it takes care of the McDonalds, O'Brian's, MacPherson's and the Jens-Michael's of the world.

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 @wordCounter INT
DECLARE @charAtPos VARCHAR(1)
DECLARE @charAtPos2 VARCHAR(2)
DECLARE @charAtPos3 VARCHAR(3)
DECLARE @wordStart INT

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

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

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

	-- 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
		SET @wordCounter = 0
	END

	IF (@wordStart = 2)
	BEGIN
		SET @wordStart = 1
	END

	IF (@wordStart = 3)
	BEGIN
		SET @wordStart = 2
	END

        -- If we encounter a McDaddy, indicate that we
	-- are about to start a word
	IF (@charAtPos2 = 'Mc') AND @wordCounter = 0
	BEGIN
		SET @wordStart = 2
	END

        -- If we encounter a MacDaddy, indicate that we
	-- are about to start a word
	IF (@charAtPos3 = 'Mac') AND @wordCounter = 0
	BEGIN
		SET @wordStart = 3
	END

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

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

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

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

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

	-- 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
	SET @WordCounter = @WordCounter + 1
END

-- Return what we have got
RETURN (@outputString)
END

Rate

Share

Share

Rate