Wednesday, 19 April 2006

Convert a string to proper case in Sql

Some nice sql code nicked from http://www32.brinkster.com/srisamp/sqlArticles/article_28.htm - Does the following:-SELECT dbo.Proper ('this is the example of a string that needs to be proper cased') OutputThis Is The Example Of A String That Needs To Be Proper Cased

Note:
If things are send in upper case, they come back in upper case, and sending in null you get '(no string passed)' back – pretty useful apart from that.



CREATE FUNCTION dbo.fnProperCase (@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 = SUBSTRING (@tcString, @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

No comments: