Proper Casing Strings
 
SQL Server provides functions that can work with strings. For example, you can use the UPPER function to convert a string to uppercase. Another common requirement is to convert a string to proper case. A proper case string is one wherein the first letter of each work is converted to uppercase. Thus, if you have a string like "john lead", after proper casing, the string would be "John Lead". In this article we will see how to write a SQL Server function that proper cases a string. Here is the overall logic that we will follow:
  • Take each word of the string.
  • Convert the first letter of the word to upper case.
  • Return the proper cased string.

Here is the source code for the function.

					
IF (OBJECT_ID ('dbo.Proper') IS NOT NULL)
	DROP FUNCTION dbo.Proper
GO
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 @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

Here is how we can test this function.

					
SELECT dbo.Proper ('this is the example of a string that needs to be proper cased')

Output
This Is The Example Of A String That Needs To Be Proper Cased

The logic works by using a space as the word seperator. Each time a new word is encountered, we reset the @wordStart variable that is used to indicate a word. This variable is then used for upper casing only the first letter of the word. Finally, the output is returned from the function.

Home