Wow not wrote any notes for a very long time…!

Recently I’ve had an issue where some text was presented in a table, and I needed to normalise the text.

For example:

TESTiNG 123

Would need to become:

Testing 123

With thanks to Justin Cooney this was possible using the SQL below:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION NormalizeCase (@InputString VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @OutputString VARCHAR(500)
DECLARE @Location INT

SET @Location = 1

-- Pre-set to a character string if the input string exists and has a length. otherwise the out string remains a NULL
IF DATALENGTH(@InputString) > 0
BEGIN
SET @OutputString = ''
END

WHILE @Location <= DATALENGTH(@InputString)
BEGIN
DECLARE @CheckCharacter VARCHAR(1)
DECLARE @PrevCheckCharacter VARCHAR(1)
DECLARE @OutCharacter VARCHAR(1)

-- Set the current character to lower case in case a capitalization condition is not met.
SELECT @CheckCharacter = LOWER(SUBSTRING(@InputString, @Location, 1))

SELECT @PrevCheckCharacter = SUBSTRING(@InputString, @Location - 1, 1)

-- Set the current letter to uppercase if the preceeding letter is a non-letter character
-- and the current character is a letter
IF @PrevCheckCharacter NOT LIKE '[a-z]'
AND @CheckCharacter LIKE '[a-z]'
BEGIN
SELECT @OutCharacter = UPPER(@CheckCharacter)
END
ELSE
BEGIN
SELECT @OutCharacter = @CheckCharacter
END

SET @OutputString = @OutputString + @OutCharacter
SET @Location = @Location + 1
END

RETURN @OutputString
END
GO

The code can be used as follows:

SELECT dbo.NormalizeCase('THIS IS a reaLLY GooD example')

With the result being:

This Is A Really Good Example