MS SQL – Normalise Case
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.
Would need to become:
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) &amp;amp;amp;gt; 0 BEGIN SET @OutputString = '' END WHILE @Location &amp;amp;amp;lt;= 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