I have a SQL function to change descriptions to Title case (first letter is a capital), but I circumstances where words need to remain as capitals e.g. brand names like IBM I want to remain as IBM and not Ibm.
the SQL function is...
CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) )
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 1
WHILE @Index <= LEN(@InputString)
SET @Char = SUBSTRING(@InputString, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InputString, @Index - 1, 1)
IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
IF @PrevChar != '''' OR UPPER(@Char) != 'S'
SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
SET @Index = @Index + 1
Can anyone help me change the function for this circumstance?
I don't have a SQL Server running but I would probably try using CHARINDEX to look for spaces (or PATINDEX if your word separators are more complex) and pull out words. Compare the UPPER version of each word with the untouched version of the word. If they are the same, leave the word alone. If they are different, convert the word to LOWER and then uppercase the first letter of the word. Move on to the next word until there are no more.