2. Home
  3. Articles
  4. Database
  5. Transact-SQL
  6. Convert to Proper Case in T-SQL

Convert to Proper Case in T-SQL

Transact-SQL
Hits: 28195
What?
So this is an article exploring how to convert UPPERCASE text into mixed case. The feed is originally for a personnel feed so it won't be converting long paragraphs of English text. Instead it will be applied to names and addresses as well as job titles and departments.

Why?
We wanted a T-SQL version despite having successfully built a custom script component for SSIS.

How?
You can search my site for how to do this in VB or C#. This version is entirely using T-SQL and we're going to use a function so no dynamic SQL (execute, evaluate).

Method #1: First version
This was my first attempt based on snippets around the web. This one has two sections, the first part capitalizes any letter after a non-alpha letter. The remainder of it is to deal with exceptions to the rule, words that you want in a specific case.
copyraw 
CREATE FUNCTION ufn_ProperCase(@Text AS VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
   -- declare some variables
   DECLARE @Reset BIT;
   DECLARE @Ret VARCHAR(8000);
   DECLARE @i INT;
   DECLARE @c VARCHAR(2);

   -- specify reset
   SELECT @Reset = 1, @i=1, @Ret = '';

   IF (UPPER(@Text)=@Text Collate Latin1_General_CS_AI)
	BEGIN

		-- cycle through each character, 
		-- if non-alpha, uppercase next alpha character.
		-- if alpha then lowercase subsequent alphas.
		-- NON-ALPHA EXCEPTIONS ADDED TO REGEXP (eg. apostrophe)
		WHILE (@i <= len(@Text))
   			SELECT @c= substring(@Text,@i,1),
               @Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c) ELSE LOWER(@c) END,
               @Reset = CASE WHEN @c like '[a-zA-Z'']' THEN 0 ELSE 1 END,
               @i = @i +1

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

		-- Any character immediately after non-alpha is now in uppercase.
		-- Base the following overrides/exceptions on this.

		-- specify exceptions to be lowercased
		SET @Ret = REPLACE(@Ret, ' And ', LOWER(' And '));
		SET @Ret = REPLACE(@Ret, ' At ', LOWER(' At '));
		SET @Ret = REPLACE(@Ret, ' Of ', LOWER(' Of '));
		SET @Ret = REPLACE(@Ret, ' The ', LOWER(' The '));

		-- specify exceptions to be uppercased
		SET @Ret = REPLACE(@Ret, ' Bi ', UPPER(' BI '));  -- Business Inteligence
		SET @Ret = REPLACE(@Ret, ' Hr ', UPPER(' HR '));  -- Human Resources
		SET @Ret = REPLACE(@Ret, ' It ', UPPER(' IT '));  -- Info Tech Services

		-- specify exceptions to be custom cased: McDonalds, Mc Donalds
		IF(@Ret LIKE '% Mc%')
			BEGIN
	   			SELECT @c = SUBSTRING(@Ret,CHARINDEX(' Mc',@Ret)+3,1)
				SET @Ret = REPLACE(@Ret, ' Mc'+LOWER(@c), ' Mc'+UPPER(@c))
			END

		-- specify exceptions to be custom cased: O'Neils
		IF(@Ret LIKE '% O''%')
			BEGIN
	   			SELECT @c = SUBSTRING(@Ret,CHARINDEX(' O''',@Ret)+3,1)
				SET @Ret = REPLACE(@Ret, ' O'''+LOWER(@c), ' O'''+UPPER(@c))
			END

		-- specify exceptions to be custom cased: D'Artagnan
		IF(@Ret LIKE '% D''%')
			BEGIN
	   			SELECT @c = SUBSTRING(@Ret,CHARINDEX(' D''',@Ret)+3,1)
				SET @Ret = REPLACE(@Ret, ' D'''+LOWER(@c), ' D'''+UPPER(@c))
			END

		-- specify exceptions to be custom cased: I'Anson
		IF(@Ret LIKE '% I''%')
			BEGIN
	   			SELECT @c = SUBSTRING(@Ret,CHARINDEX(' I''',@Ret)+3,1)
				SET @Ret = REPLACE(@Ret, ' I'''+LOWER(@c), ' I'''+UPPER(@c))
			END

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

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

   RETURN @Ret

END
  1.  CREATE FUNCTION ufn_ProperCase(@Text AS VARCHAR(8000)) 
  2.  RETURNS VARCHAR(8000) 
  3.  AS 
  4.  BEGIN 
  5.     -- declare some variables 
  6.     DECLARE @Reset BIT; 
  7.     DECLARE @Ret VARCHAR(8000); 
  8.     DECLARE @i INT; 
  9.     DECLARE @c VARCHAR(2); 
  10.   
  11.     -- specify reset 
  12.     SELECT @Reset = 1, @i=1, @Ret = ''; 
  13.   
  14.     IF (UPPER(@Text)=@Text Collate Latin1_General_CS_AI) 
  15.      BEGIN 
  16.   
  17.          -- cycle through each character, 
  18.          -- if non-alpha, uppercase next alpha character. 
  19.          -- if alpha then lowercase subsequent alphas. 
  20.          -- NON-ALPHA EXCEPTIONS ADDED TO REGEXP (eg. apostrophe) 
  21.          WHILE (@i <= len(@Text)) 
  22.                 SELECT @c= substring(@Text,@i,1), 
  23.                 @Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c) ELSE LOWER(@c) END, 
  24.                 @Reset = CASE WHEN @c like '[a-zA-Z'']' THEN 0 ELSE 1 END, 
  25.                 @i = @i +1 
  26.   
  27.          -- add a leading and trailing space to indicate word delimiters (bol & eol) 
  28.          SET @Ret = ' ' + @Ret + ' '; 
  29.   
  30.          -- Any character immediately after non-alpha is now in uppercase. 
  31.          -- Base the following overrides/exceptions on this. 
  32.   
  33.          -- specify exceptions to be lowercased 
  34.          SET @Ret = REPLACE(@Ret, ' And ', LOWER(' And ')); 
  35.          SET @Ret = REPLACE(@Ret, ' At ', LOWER(' At ')); 
  36.          SET @Ret = REPLACE(@Ret, ' Of ', LOWER(' Of ')); 
  37.          SET @Ret = REPLACE(@Ret, ' The ', LOWER(' The ')); 
  38.   
  39.          -- specify exceptions to be uppercased 
  40.          SET @Ret = REPLACE(@Ret, ' Bi ', UPPER(' BI '));  -- Business Inteligence 
  41.          SET @Ret = REPLACE(@Ret, ' Hr ', UPPER(' HR '));  -- Human Resources 
  42.          SET @Ret = REPLACE(@Ret, ' It ', UPPER(' IT '));  -- Info Tech Services 
  43.   
  44.          -- specify exceptions to be custom cased: McDonalds, Mc Donalds 
  45.          IF(@Ret LIKE '% Mc%') 
  46.              BEGIN 
  47.                     SELECT @c = SUBSTRING(@Ret,CHARINDEX(' Mc',@Ret)+3,1) 
  48.                  SET @Ret = REPLACE(@Ret, ' Mc'+LOWER(@c), ' Mc'+UPPER(@c)) 
  49.              END 
  50.   
  51.          -- specify exceptions to be custom cased: O'Neils 
  52.          IF(@Ret LIKE '% O''%') 
  53.              BEGIN 
  54.                     SELECT @c = SUBSTRING(@Ret,CHARINDEX(' O''',@Ret)+3,1) 
  55.                  SET @Ret = REPLACE(@Ret, ' O'''+LOWER(@c), ' O'''+UPPER(@c)) 
  56.              END 
  57.   
  58.          -- specify exceptions to be custom cased: D'Artagnan 
  59.          IF(@Ret LIKE '% D''%') 
  60.              BEGIN 
  61.                     SELECT @c = SUBSTRING(@Ret,CHARINDEX(' D''',@Ret)+3,1) 
  62.                  SET @Ret = REPLACE(@Ret, ' D'''+LOWER(@c), ' D'''+UPPER(@c)) 
  63.              END 
  64.   
  65.          -- specify exceptions to be custom cased: I'Anson 
  66.          IF(@Ret LIKE '% I''%') 
  67.              BEGIN 
  68.                     SELECT @c = SUBSTRING(@Ret,CHARINDEX(' I''',@Ret)+3,1) 
  69.                  SET @Ret = REPLACE(@Ret, ' I'''+LOWER(@c), ' I'''+UPPER(@c)) 
  70.              END 
  71.   
  72.          -- remove leading and trailing spaces 
  73.          SET @Ret = LTRIM(RTRIM(@Ret)); 
  74.   
  75.      END 
  76.     ELSE 
  77.      -- return the string unaffected if it is not in uppercase 
  78.      SET @Ret=@Text 
  79.   
  80.     RETURN @Ret 
  81.   
  82.  END 
Tip: Snippet to remove this function
Add this to the beginning if you want to test the function a lot. I didn't include this in case you already have a function called this.
copyraw 
IF object_id(N'ufn_ProperCase', N'FN') IS NOT NULL
        DROP FUNCTION ufn_ProperCase
GO
  1.  IF object_id(N'ufn_ProperCase', N'FN') IS NOT NULL 
  2.          DROP FUNCTION ufn_ProperCase 
  3.  GO 
Method #2: Revised version
This version will use a variable table but it could be adapted to feed off any database table. As before it capitalizes words after non-alpha letters as well as apostrophe, but some name exceptions are built into the same case statement (McDonalds, O'Neils, D'Artagnan) and hopefully a clear way on how to add more.

Then I have the custom exceptions, here simply type the word as it is intended to ultimately display as, the case-insensitive replace by T-SQL will find that instance and replace it with exactly how the word is written in the function.
copyraw 
CREATE FUNCTION ufn_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 @CaseExceptions TABLE(MyIndex int identity(1,1), MyString VARCHAR(8000));
   DECLARE @exceptionToFind VARCHAR(8000);

   -- specify reset
   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
                                                -- EXCEPTION: McDonalds and Mc Donalds but not Tomcat
                                                WHEN @c0 = ' ' AND @c1 = 'M' AND @c2 = 'c' THEN 1

                                                -- EXCEPTION: D'Artagnan and I'Anson
                                                -- EXCEPTION: O'Neils and O' Sullivan but not Mo'Money
                                                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


                -- remove leading spaces so first words will not be lowercased
                -- (eg. "The" at the beginning of a sentence)
                SET @Ret = LTRIM(@Ret);

                -- custom exceptions: the search is case-insensitive and will
                -- convert the word to the case as written below.
                INSERT INTO @CaseExceptions (MyString) VALUES
                ('and'), ('at'), ('of'), ('the')                -- common conversions
                ,('BI')                                         -- Business Inteligence
                ,('HR')                                         -- Human Resources
                ,('IT')                                         -- Info Tech Services

                DECLARE exceptions_cursor CURSOR FOR
                        SELECT ' '+MyString+' ' FROM @CaseExceptions
                OPEN exceptions_cursor
                        FETCH NEXT FROM exceptions_cursor
                                INTO @exceptionToFind;
                WHILE @@FETCH_STATUS = 0
                        BEGIN
                                SET @Ret = REPLACE(@Ret, @exceptionToFind, @exceptionToFind)

                                FETCH NEXT FROM exceptions_cursor INTO @exceptionToFind;
                        END
                CLOSE exceptions_cursor
                DEALLOCATE exceptions_cursor


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

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


   RETURN @Ret

END
  1.  CREATE FUNCTION ufn_ProperCase(@Text AS VARCHAR(8000)) 
  2.  RETURNS VARCHAR(8000) 
  3.  AS 
  4.  BEGIN 
  5.     -- declare some variables 
  6.     DECLARE @Reset BIT; 
  7.     DECLARE @Ret VARCHAR(8000); 
  8.     DECLARE @i INT; 
  9.     DECLARE @c0 CHAR(1); 
  10.     DECLARE @c1 CHAR(1); 
  11.     DECLARE @c2 CHAR(1); 
  12.     DECLARE @CaseExceptions TABLE(MyIndex int identity(1,1), MyString VARCHAR(8000)); 
  13.     DECLARE @exceptionToFind VARCHAR(8000); 
  14.   
  15.     -- specify reset 
  16.     SELECT @Reset = 1, @i=1, @Ret = ''; 
  17.   
  18.     -- only apply if all characters are already in uppercase 
  19.     IF (UPPER(@Text)=@Text COLLATE Latin1_General_CS_AI) 
  20.          BEGIN 
  21.   
  22.                  -- add a leading and trailing space to indicate word delimiters (bol & eol) 
  23.                  SET @Text = ' ' + @Text + ' '; 
  24.   
  25.                  -- cycle through each character, 
  26.                  -- if non-alpha, uppercase next alpha character. 
  27.                  -- if alpha then lowercase subsequent alphas. 
  28.                  WHILE (@i <= LEN(@Text)) 
  29.                          SELECT 
  30.                                  @c0=SUBSTRING(@Text,@i-2,1), @c1=SUBSTRING(@Text,@i-1,1), @c2=SUBSTRING(@Text,@i,1), 
  31.                                  @Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c2) ELSE LOWER(@c2) END, 
  32.                                  @Reset = CASE 
  33.                                                  -- EXCEPTION: McDonalds and Mc Donalds but not Tomcat 
  34.                                                  WHEN @c0 = ' ' AND @c1 = 'M' AND @c2 = 'c' THEN 1 
  35.   
  36.                                                  -- EXCEPTION: D'Artagnan and I'Anson 
  37.                                                  -- EXCEPTION: O'Neils and O' Sullivan but not Mo'Money 
  38.                                                  WHEN @c0 = ' ' AND @c1 IN ('D', 'I', 'O') AND @c2 = '''' THEN 1 
  39.   
  40.                                                  WHEN @c2 LIKE '[a-zA-Z'']' THEN 0       -- Apply LOWER to any character after alphas or apostrophes 
  41.                                                  ELSE 1                                  -- Apply UPPER to any character after symbols/punctuation 
  42.                                          END, 
  43.                                  @i = @i +1 
  44.   
  45.   
  46.                  -- remove leading spaces so first words will not be lowercased 
  47.                  -- (eg. "The" at the beginning of a sentence) 
  48.                  SET @Ret = LTRIM(@Ret); 
  49.   
  50.                  -- custom exceptions: the search is case-insensitive and will 
  51.                  -- convert the word to the case as written below. 
  52.                  INSERT INTO @CaseExceptions (MyString) VALUES 
  53.                  ('and'), ('at'), ('of'), ('the')                -- common conversions 
  54.                  ,('BI')                                         -- Business Inteligence 
  55.                  ,('HR')                                         -- Human Resources 
  56.                  ,('IT')                                         -- Info Tech Services 
  57.   
  58.                  DECLARE exceptions_cursor CURSOR FOR 
  59.                          SELECT ' '+MyString+' ' FROM @CaseExceptions 
  60.                  OPEN exceptions_cursor 
  61.                          FETCH NEXT FROM exceptions_cursor 
  62.                                  INTO @exceptionToFind; 
  63.                  WHILE @@FETCH_STATUS = 0 
  64.                          BEGIN 
  65.                                  SET @Ret = REPLACE(@Ret, @exceptionToFind, @exceptionToFind) 
  66.   
  67.                                  FETCH NEXT FROM exceptions_cursor INTO @exceptionToFind; 
  68.                          END 
  69.                  CLOSE exceptions_cursor 
  70.                  DEALLOCATE exceptions_cursor 
  71.   
  72.   
  73.                  -- remove any leading and trailing spaces 
  74.                  SET @Ret = LTRIM(RTRIM(@Ret)); 
  75.   
  76.          END 
  77.     ELSE 
  78.          BEGIN 
  79.                  -- return the string unaffected if it is not in uppercase 
  80.                  SET @Ret=@Text 
  81.          END 
  82.   
  83.   
  84.     RETURN @Ret 
  85.   
  86.  END 

Method #3: No Cursor
Personally, I felt the cursor allowed your exception list to be longer and to be run off a populated SQL table, but if it's a short one like I was tasked with, then you can put the words of mixed case in a comma delimited string. The string would contain the case of your choosing and using T-SQL case-insensitivity will overwrite whatever match it finds with what you typed in the code. Here's my final version:
copyraw 
CREATE FUNCTION dbo.ufn_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
                                                -- EXCEPTION: McDonalds and Mc Donalds but not Tomcat
                                                WHEN @c0 = ' ' AND @c1 = 'M' AND @c2 = 'c' THEN 1

                                                -- EXCEPTION: D'Artagnan, I'Anson, O'Neils and O' Sullivan but Mo'Money
                                                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 = 'and,at,de,for,in,of,or,the,to,van,y,'+
                        'BI,GP,HR,IT,NHS,OVC,'+
                        'PA,PR,PT,PTHP,UET,UK,USA,VC,'+
                        'AskMe,HoD,PO Box,SportMe,'

                -- 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
  1.  CREATE FUNCTION dbo.ufn_ProperCase(@Text AS VARCHAR(8000)) 
  2.  RETURNS VARCHAR(8000) 
  3.  AS 
  4.  BEGIN 
  5.     -- declare some variables 
  6.     DECLARE @Reset BIT; DECLARE @Ret VARCHAR(8000); DECLARE @i INT; 
  7.     DECLARE @c0 CHAR(1); DECLARE @c1 CHAR(1); DECLARE @c2 CHAR(1); 
  8.     DECLARE @CaseLen INT; 
  9.     DECLARE @CaseExceptions VARCHAR(8000); 
  10.     DECLARE @CaseValue VARCHAR(8000); 
  11.   
  12.     -- Set some default values 
  13.     SELECT @Reset = 1, @i=1, @Ret = ''; 
  14.   
  15.     -- only apply if all characters are already in uppercase 
  16.     IF (UPPER(@Text)=@Text COLLATE Latin1_General_CS_AI) 
  17.          BEGIN 
  18.   
  19.                  -- add a leading and trailing space to indicate word delimiters (bol & eol) 
  20.                  SET @Text = ' ' + @Text + ' '; 
  21.   
  22.                  -- cycle through each character, 
  23.                  -- if non-alpha, uppercase next alpha character. 
  24.                  -- if alpha then lowercase subsequent alphas. 
  25.                  WHILE (@i <= LEN(@Text)) 
  26.                          SELECT 
  27.                                  @c0=SUBSTRING(@Text,@i-2,1), @c1=SUBSTRING(@Text,@i-1,1), @c2=SUBSTRING(@Text,@i,1), 
  28.                                  @Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c2) ELSE LOWER(@c2) END, 
  29.                                  @Reset = CASE 
  30.                                                  -- EXCEPTION: McDonalds and Mc Donalds but not Tomcat 
  31.                                                  WHEN @c0 = ' ' AND @c1 = 'M' AND @c2 = 'c' THEN 1 
  32.   
  33.                                                  -- EXCEPTION: D'Artagnan, I'Anson, O'Neils and O' Sullivan but Mo'Money 
  34.                                                  WHEN @c0 = ' ' AND @c1 IN ('D', 'I', 'O') AND @c2 = '''' THEN 1 
  35.   
  36.                                                  WHEN @c2 LIKE '[a-zA-Z'']' THEN 0               -- Apply LOWER to any character after alphas or apostrophes 
  37.                                                  ELSE 1                                                                  -- Apply UPPER to any character after symbols/punctuation 
  38.                                          END, 
  39.                                  @i = @i +1 
  40.   
  41.                  -- add a trailing space in case the previous rule changed this. 
  42.                  SET @Ret = @Ret + ' '; 
  43.   
  44.                  -- custom exceptions: this search is case-insensitive and will 
  45.                  -- replace the word to the case as it is written in the list. 
  46.                  -- NOTE: this list has to end with a comma! 
  47.                  SELECT @i=0, @CaseLen=0, 
  48.                          @CaseExceptions = 'and,at,de,for,in,of,or,the,to,van,y,'+ 
  49.                          'BI,GP,HR,IT,NHS,OVC,'+ 
  50.                          'PA,PR,PT,PTHP,UET,UK,USA,VC,'+ 
  51.                          'AskMe,HoD,PO Box,SportMe,' 
  52.   
  53.                  -- Loop through exception cases 
  54.                  WHILE CHARINDEX(',', @CaseExceptions, @i+1)>0 
  55.                          BEGIN 
  56.                                  -- get the delimited word 
  57.                                  SET @CaseLen = CHARINDEX(',', @CaseExceptions, @i+1) - @i 
  58.                                  SET @CaseValue = SUBSTRING(@CaseExceptions, @i, @CaseLen) 
  59.   
  60.                                  -- replace it in the original text 
  61.                                  SET @Ret = REPLACE(@Ret, ' '+@CaseValue+' ', ' '+@CaseValue+' ') 
  62.   
  63.                                  -- get position of next word 
  64.                                  SET @i = CHARINDEX(',', @CaseExceptions, @i+@CaseLen) +1 
  65.                          END 
  66.   
  67.                  -- remove any leading and trailing spaces 
  68.                  SET @Ret = LTRIM(RTRIM(@Ret)); 
  69.   
  70.                  -- capitalize first character of data irrespective of previous rules 
  71.                  SET @Ret = UPPER(SUBSTRING(@Ret,1,1)) + SUBSTRING(@Ret,2,LEN(@Ret)); 
  72.   
  73.          END 
  74.     ELSE 
  75.      BEGIN 
  76.                  -- return the string unaffected if it is not in uppercase 
  77.                  SET @Ret=@Text 
  78.          END 
  79.   
  80.     RETURN @Ret 
  81.  END 

Examples of what this script does:
Reminder: The script only affects data already in UPPERCASE. Change the IF (UPPER(@Text)=@Text COLLATE Latin1_General_CS_AI) line if you want to change this.

OriginalProper-Cased
JOHN SMITH-JONESJohn Smith-Jones
HEAD OF IT SERVICESHead of IT Services
FLAT 123AFlat 123A
MRS. O'CONNOR-MCDONALDMrs. O'Connor-McDonald
ORG_DEPT_STATUSOrg_Dept_Status
SEND.HELP.NOWSend.Help.Now
PO BOX 1234PO Box 1234
B'MTH BH1 3ABB'mth Bh1 3Ab
123 THE WORLD'S END123 the World's End
'WONDERWALL''wonderwall'
"FAIRLANDS""Fairlands"
C/O DAD'S (JIMMY)C/O Dad's (Jimmy)
SampleTextSampleText
lowercase.textlowercase.text

Examples of usage:
copyraw 
SELECT
    ufn_ProperCase([Surname])
FROM
    employees
  1.  SELECT 
  2.      ufn_ProperCase([Surname]) 
  3.  FROM 
  4.      employees 


Additional:
Note how the above doesn't convert postal codes, specifically British postcodes, which isn't included in the examples above because the function was only going to be applied to names and titles rather than addresses, but here it is anyway, add this after the "loop through exception cases" while clause:
copyraw 
-- Now for some UK (British) PostCode recognition
SELECT @i=1, @CaseExceptions = @Ret;
WHILE CHARINDEX(' ', @CaseExceptions, @i+1)>0
        BEGIN
                -- get the delimited word
                SET @CaseLen = CHARINDEX(' ', @CaseExceptions, @i+1) - @i
                SET @CaseValue = SUBSTRING(@CaseExceptions, @i, @CaseLen)

                -- Is this word a UK postal outward code (2 letters and number(s))
                IF ((LEN(@CaseValue)<=4) AND (SUBSTRING(@CaseValue, 1, 1) LIKE '[a-zA-Z]') AND (SUBSTRING(@CaseValue, 2, 1) LIKE '[a-zA-Z]') AND (SUBSTRING(@CaseValue, 3, 1) LIKE '[0-9]'))
                        SET @CaseValue = UPPER(@CaseValue);

                -- Is this word a UK postal inward code (number followed by letters)
                IF ((LEN(@CaseValue)<=4) AND (SUBSTRING(@CaseValue, 1, 1) LIKE '[0-9]') AND (SUBSTRING(@CaseValue, 2, 1) LIKE '[a-zA-Z]') AND (SUBSTRING(@CaseValue, 3, 1) LIKE '[a-zA-Z]'))
                        SET @CaseValue = UPPER(@CaseValue);

                SET @Ret = REPLACE(@Ret, ' '+@CaseValue+' ', ' '+@CaseValue+' ')

                -- get position of next word
                SET @i = CHARINDEX(' ', @CaseExceptions, @i+@CaseLen) +1
        END
  1.  -- Now for some UK (British) PostCode recognition 
  2.  SELECT @i=1, @CaseExceptions = @Ret; 
  3.  WHILE CHARINDEX(' ', @CaseExceptions, @i+1)>0 
  4.          BEGIN 
  5.                  -- get the delimited word 
  6.                  SET @CaseLen = CHARINDEX(' ', @CaseExceptions, @i+1) - @i 
  7.                  SET @CaseValue = SUBSTRING(@CaseExceptions, @i, @CaseLen) 
  8.   
  9.                  -- Is this word a UK postal outward code (2 letters and number(s)) 
  10.                  IF ((LEN(@CaseValue)<=4) AND (SUBSTRING(@CaseValue, 1, 1) LIKE '[a-zA-Z]') AND (SUBSTRING(@CaseValue, 2, 1) LIKE '[a-zA-Z]') AND (SUBSTRING(@CaseValue, 3, 1) LIKE '[0-9]')) 
  11.                          SET @CaseValue = UPPER(@CaseValue); 
  12.   
  13.                  -- Is this word a UK postal inward code (number followed by letters) 
  14.                  IF ((LEN(@CaseValue)<=4) AND (SUBSTRING(@CaseValue, 1, 1) LIKE '[0-9]') AND (SUBSTRING(@CaseValue, 2, 1) LIKE '[a-zA-Z]') AND (SUBSTRING(@CaseValue, 3, 1) LIKE '[a-zA-Z]')) 
  15.                          SET @CaseValue = UPPER(@CaseValue); 
  16.   
  17.                  SET @Ret = REPLACE(@Ret, ' '+@CaseValue+' ', ' '+@CaseValue+' ') 
  18.   
  19.                  -- get position of next word 
  20.                  SET @i = CHARINDEX(' ', @CaseExceptions, @i+@CaseLen) +1 
  21.          END 


Category: Transact-SQL :: Article: 565

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.