Technical Article

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