只是好奇是否有更好的方法將每個單詞的第一個字母大寫，解決字串拆分可能出現的排序問題，更容易包含其他分隔符。
這用于報告，最好不要使用用戶定義的函式，以便代碼可以在報告中。
代碼可以擴展以包含額外的分隔符，這里包含了兩個，但這是最好的方法嗎？
/* 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];
以下是僅搜索一個分隔符的代碼，如果這樣更容易理解：
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];
以及搜索兩個分隔符的代碼，但未糾正可能的字串拆分順序問題：
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];
uj5u.com熱心網友回復：
從@Larnu喜歡的答案開始
/* 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;
然后我最終在 SSRS 中使用了 VB 函式。感謝您的意見 @Larnu
=StrConv(Fields!Column.Value, vbProperCase)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/492724.html