Proper/Title Case a Column with Exceptions table in SQL Server

Question

I am trying to convert a column which is in upper case to proper case but with exceptions like certain acronyms, abbreviations. I am following the below code to implement that. But looks like this will be an ongoing process and so, I want to create a table with the exceptions in order to make it easy to clean the data and I want to be able to call the exceptions table from the function. It would be great if anyone can help me with any codes they have which is similar to this or any ideas on how to implement it.

ALTER FUNCTION [dbo].[Business_ProperCase]
     (@Text AS VARCHAR(8000)) 
RETURNS VARCHAR(8000) 
AS 
BEGIN 
    -- declare some variables 
    DECLARE @Reset BIT; DECLARE @Ret VARCHAR(8000); DECLARE @i INT; 
    DECLARE @c0 CHAR(1); DECLARE @c1 CHAR(1); DECLARE @c2 CHAR(1); 
    DECLARE @CaseLen INT; 
    DECLARE @CaseExceptions VARCHAR(8000); 
    DECLARE @CaseValue VARCHAR(8000); 

    -- Set some default values 
    SELECT @Reset = 1, @i=1, @Ret = ''; 

    -- only apply if all characters are already in uppercase 
    IF (UPPER(@Text)=@Text COLLATE Latin1_General_CS_AI) 
         BEGIN 

                 -- add a leading and trailing space to indicate word delimiters (bol & eol) 
                 SET @Text = ' ' + @Text + ' '; 

                 -- cycle through each character, 
                 -- if non-alpha, uppercase next alpha character. 
                 -- if alpha then lowercase subsequent alphas. 
                 WHILE (@i <= LEN(@Text)) 
                         SELECT 
                                 @c0=SUBSTRING(@Text,@i-2,1), @c1=SUBSTRING(@Text,@i-1,1), @c2=SUBSTRING(@Text,@i,1), 
                                 @Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c2) ELSE LOWER(@c2) END, 
                                 @Reset = CASE 
                                                                                             WHEN @c0 = ' ' AND @c1 = 'M' AND @c2 = 'c' THEN 1 

                                                 WHEN @c0 = ' ' AND @c1 IN ('D', 'I', 'O') AND @c2 = '''' THEN 1 


                                                 WHEN @c2 LIKE '[a-zA-Z'']' THEN 0               -- Apply LOWER to any character after alphas or apostrophes 
                                                ELSE 1                                                                  -- Apply UPPER to any character after symbols/punctuation 
                                         END, 
                                 @i = @i +1 

                 -- add a trailing space in case the previous rule changed this. 
                 SET @Ret = @Ret + ' '; 

                 -- custom exceptions: this search is case-insensitive and will 
                 -- replace the word to the case as it is written in the list. 
                 -- NOTE: this list has to end with a comma! 
                 SELECT @i=0, @CaseLen=0, 
                         @CaseExceptions = 'ABS,LLC,MD,MBA,MA,

--Want to create a table for these exceptions and call them from this function 
                 -- Loop through exception cases 
                 WHILE CHARINDEX(',', @CaseExceptions, @i+1)>0 
                         BEGIN 
                                 -- get the delimited word 
                                 SET @CaseLen = CHARINDEX(',', @CaseExceptions, @i+1) - @i 
                                 SET @CaseValue = SUBSTRING(@CaseExceptions, @i, @CaseLen) 

                                 -- replace it in the original text 
                                 SET @Ret = REPLACE(@Ret, ' '+@CaseValue+' ', ' '+@CaseValue+' ') 

                                 -- get position of next word 
                                 SET @i = CHARINDEX(',', @CaseExceptions, @i+@CaseLen) +1 
                         END 

                 -- remove any leading and trailing spaces 
                 SET @Ret = LTRIM(RTRIM(@Ret)); 

                 -- capitalize first character of data irrespective of previous rules 
                 SET @Ret = UPPER(SUBSTRING(@Ret,1,1)) + SUBSTRING(@Ret,2,LEN(@Ret)); 

         END 
    ELSE 
     BEGIN 
                 -- return the string unaffected if it is not in uppercase 
                 SET @Ret=@Text 
         END 

    RETURN @Ret 
 END 

Answer 1

Create a table (I use TITLE_CASE_EXCEPTION as my example) with a column EXCEPTION Then it is data driven from there.

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[GUI].[fn_TITLE_CASE]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
        DROP FUNCTION [GUI].[fn_TITLE_CASE]
    GO

    CREATE FUNCTION [GUI].[fn_TITLE_CASE] 
        (
        @STRING VARCHAR(MAX)
        )
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
        SET QUOTED_IDENTIFIER OFF

        DECLARE @RESET BIT
        DECLARE @_OUT_STRING VARCHAR(MAX)
        DECLARE @I INT
        DECLARE @C CHAR(1)

        DECLARE @CASE_LEN INT = 0
        DECLARE @CASE_EXCEPTIONS VARCHAR(MAX) = ''
        DECLARE @CASE_VALUE VARCHAR(MAX) = ''

        IF @STRING IS NULL
            RETURN NULL
        IF @STRING = ''
            RETURN @STRING

        SELECT @STRING = LOWER(RTRIM(@STRING)), @RESET = 1, @I = 1, @_OUT_STRING = ''

        WHILE (@I <= LEN(@STRING))
        SELECT 
            @C = SUBSTRING(@STRING, @I, 1),
            @_OUT_STRING = @_OUT_STRING + CASE WHEN @RESET = 1 THEN UPPER(@C) ELSE @C END,
            @RESET = CASE WHEN @C LIKE '[a-zA-Z'']' THEN 0 ELSE 1 END,
            @I = @I + 1

        SELECT @I = 0, @_OUT_STRING = @_OUT_STRING + ' '
        SELECT @CASE_EXCEPTIONS = @CASE_EXCEPTIONS + RTRIM(EXCEPTION) + ',' FROM [LOOKUP].TITLE_CASE_EXCEPTION

        WHILE CHARINDEX(',', @CASE_EXCEPTIONS, @I + 1) > 0 
            BEGIN 
            -- get the delimited word 
            SET @CASE_LEN = CHARINDEX(',', @CASE_EXCEPTIONS, @I + 1) - @I
            SET @CASE_VALUE = SUBSTRING(@CASE_EXCEPTIONS, @I, @CASE_LEN) 

            -- replace it in the original text 
            SET @_OUT_STRING = REPLACE(@_OUT_STRING, ' ' + @CASE_VALUE + ' ', ' ' + @CASE_VALUE + ' ') 

            -- get position of next word 
            SET @I = CHARINDEX(',', @CASE_EXCEPTIONS, @I + @CASE_LEN) + 1 
            END

        RETURN RTRIM(@_OUT_STRING)
    END
    GO