Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Detective Stories - Changing the Case Expand / Collapse
Author
Message
Posted Wednesday, April 13, 2011 11:08 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 5, 2014 12:21 PM
Points: 78, Visits: 193
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."
Post #1093029
Posted Wednesday, April 13, 2011 11:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 5,584, Visits: 6,379
That's an interesting piece of code. Thanks for posting it.

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1093052
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse