SQL Capitalize first letter except 3 letter words-CodePudding

I need an SQL statement (function) to capitalize the first letter of each word the other characters have to be lower case. The part that i don't know how to do it is that i need to keep the structure of 3 letter words

Words can be like :

DOT FOODS , dot foods , CANADA COMPANY INC , CANADA COMPANY inc ,

Expected :

DOT Foods , dot Foods , Canada Company INC , Canada Company inc ,

With this function i get everything i need except the last part with the 3 words :

CREATE FUNCTION CAP(@String VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
  BEGIN
      DECLARE @Position INT;

      SELECT @String = STUFF(Lower(@String), 1, 1, UPPER(LEFT(@String, 1)))
                       COLLATE
                              Latin1_General_Bin,
             @Position = PATINDEX('%[^A-Za-z''][a-z]%',
                         @String COLLATE Latin1_General_Bin);

      WHILE @Position > 0
        SELECT @String = STUFF(@String, @Position, 2, UPPER(
                                             Substring(@String, @Position, 2)))
                                                    COLLATE
                                                    Latin1_General_Bin,
               @Position = PATINDEX('%[^A-Za-z''][a-z]%',
                           @String COLLATE Latin1_General_Bin);

      RETURN @String;
  END;

CodePudding user response：

As you're on Azure SQL Database you have access to STRING_SPLIT's new parameter, ordinal. This means you can therefore split the string at each white space (' ') and have the ordinal position return. Then you can re-aggregate the string using the ordinal column to maintain the order, and use a CASE expression to check the length of the value; if it's 3 (or fewer) characters leave it as is, however, if it isn't UPPER the first character and LOWER the rest:

DECLARE @YourString varchar(8000) = 'DOT FOODS , dot foods , CANADA COMPANY, INC CANADA COMPANY, inc';

SELECT STRING_AGG(CASE WHEN LEN(SS.value) <= 3 THEN SS.value
                       ELSE UPPER(LEFT(SS.Value,1))   LOWER(STUFF(SS.Value,1,1,''))
                  END,' ') WITHIN GROUP (ORDER BY SS.ordinal)
FROM STRING_SPLIT(@YourString, ' ',1) SS;

db<>fiddle