Detective Stories - Changing the Case

  • We use the function below, i.e. fProper(fieldname). Works great.

    USE [SfiData]

    GO

    /****** Object: UserDefinedFunction [dbo].[fProper] Script Date: 04/13/2011 13:05:27 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER FUNCTION [dbo].[fProper] (@tcString VARCHAR(100))

    RETURNS VARCHAR(100) AS

    BEGIN

    -- Scratch variables used for processing

    DECLARE @workString VARCHAR(100)

    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 @workString = LOWER(@tcString)

    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 = SUBSTRING (@workString, @loopCounter, 1)

    -- If we are the start of a word, uppercase the character

    -- and reset the work 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

    Wallace Houston
    Sunnyland Farms, Inc.

    "We must endeavor to persevere."

  • That's an interesting piece of code. Thanks for posting it.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 2 posts - 31 through 32 (of 32 total)

You must be logged in to reply to this topic. Login to reply