Capitalize first letter, every word, fix possible string split order issues, multiple delimiters in T-SQL 2017 without using a user-defined function

Question

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];

I like [this answer](https://www.sqlservercentral.com/forums/topic/first-letter-capital-of-the-word#post-4005494) over on SSC. Though to repeat [my opinion](https://www.sqlservercentral.com/forums/topic/first-letter-capital-of-the-word#post-4005470), this is a task for your application layer, not SQL. — Thom A, Jun 13 '22 at 17:10
Whatever you do is going to be terrible, and should better be in the application layer. At best, you can create an SQLCLR function that will do this. T-SQL is really bad at string manipulation. — Charlieface, Jun 13 '22 at 23:42
Yes, there's an infinitely better solution. SQL Server 2017 allows execution of Python scripts on query results with `sp_execute_external_script` as part of its ML services. Use a python script for what you want. SQL, not just T-SQL, are terrible at string manipulation and what you ask is a complex problem already — Panagiotis Kanavos, Jun 14 '22 at 15:55
Explain the actual problem, the requirements and examples. The SQL queries are too complex to explain anything. It's quite possible SQL Server already includes the necessary packages — Panagiotis Kanavos, Jun 14 '22 at 15:56

Daniel · Accepted Answer · 2022-06-15T21:25:45.943

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)

Capitalize first letter, every word, fix possible string split order issues, multiple delimiters in T-SQL 2017 without using a user-defined function

1 Answers1