Capitalize first letter, every word, fix possible string split order issues, multiple delimiters in-CodePudding

Just curious if there is a better way to capitalize first letter of every word, fixing possible ordering issues with string split, that is easier to include other delimiters.

This is used for reports, it would be preferable not to use a user-defined function so the code can be in the report.

The code can be expanded to include additional delimiters, two were included here, yet is this the best way?

/* Capitalize first letter of every word, fixing possible ordering issues with string split, including words following a dash in ('-') */
SELECT RIGHT(((SELECT ' ' (UPPER(LEFT([value], 1)) SUBSTRING([value], 2, LEN([Column])))
               FROM(SELECT [value], ROW_NUMBER() OVER (ORDER BY [pointer]) AS [p]
                    FROM(SELECT [value], CHARINDEX(' ' [value] ' ', ' ' [Column] ' ') AS [pointer]
                         FROM STRING_SPLIT((SELECT '-' (UPPER(LEFT([value], 1)) LOWER(SUBSTRING([value], 2, LEN([Column]))))
                                            FROM(SELECT [value], ROW_NUMBER() OVER (ORDER BY [pointer]) AS [p2]
                                                 FROM(SELECT [value], CHARINDEX('-' [value] '-', '-' [Column] '-') AS [pointer]
                                                      FROM STRING_SPLIT([Column], '-')) AS [sp2] ) AS [s2]
                                           FOR XML PATH('')), ' ')) AS [sp] ) AS [s]
              FOR XML PATH(''))), LEN([Column])) AS [Column_Cased]
FROM [Database].[Table];

Here is the code only searching for one delimiter if that makes it easier to understand:

SELECT (SELECT ' ' (UPPER(LEFT([value], 1)) LOWER(SUBSTRING([value], 2, LEN([Column]))))
        FROM(SELECT [value], ROW_NUMBER() OVER (ORDER BY [pointer]) AS [p]
             FROM(SELECT [value], CHARINDEX(' ' [value] ' ', ' ' [Column] ' ') AS [pointer]
                  FROM STRING_SPLIT([Column], ' ')) AS [sp] ) AS [s]
       FOR XML PATH('')) AS [Column_Cased]
FROM [Database].[Table];

And the code searching for two delimiters, yet not correcting possible string split order issues:

SELECT RIGHT(((SELECT ' ' (UPPER(LEFT([value], 1)) SUBSTRING([value], 2, LEN([Column])))
               FROM(SELECT [value]
                    FROM STRING_SPLIT((SELECT '-' (UPPER(LEFT([value], 1)) LOWER(SUBSTRING([value], 2, LEN([Column]))))
                                       FROM(SELECT [value] FROM STRING_SPLIT([Column], '-')) AS [s2]
                                      FOR XML PATH('')), ' ')) AS [s]
              FOR XML PATH(''))), LEN([Column])) AS [Column_Cased]
FROM [Database].[Table];

CodePudding user response：

Started from what @Larnu liked from this answer

/* Following spaces */
IF CHARINDEX(' ', @string)<>0 BEGIN
    DECLARE @i INT=@first;
    DECLARE @delimiter CHAR(1) =' ';
    WHILE @i<=@last BEGIN
        SET @string=REPLACE(@string, @delimiter CHAR(@i), @delimiter CHAR(@i));
        SET @i=@i 1;
    END;
END;
/* Following dashes */
IF CHARINDEX('-', @string)<>0 BEGIN
    SET @i=@first;
    SET @delimiter='-';
    WHILE @i<=@last BEGIN
        SET @string=REPLACE(@string, @delimiter CHAR(@i), @delimiter CHAR(@i));
        SET @i=@i 1;
    END;
END;
/* First Letter */
SET @string=UPPER(LEFT(@string, 1)) RIGHT(@string, LEN(@string)-1);
RETURN @string;

Then I ended up using the VB function in SSRS. Thanks for your opinion @Larnu

=StrConv(Fields!Column.Value, vbProperCase)