3374. 首字母大写 II

表：user_content

将每个单词的第一个字母转换为大写，其余字母保持小写。
特殊处理包含特殊字符的单词：
对于用短横 - 连接的词语，两个部份都应该大写（例如，top-rated → Top-Rated）
所有其他格式和空格应保持不变
返回结果表同时包含原始的 content_text 以及根据上述规则修改后的文本。

结果格式如下例所示。

示例：

输入：

user_content 表：

±-----------±--------------------------------+
| content_id | content_text |
±-----------±--------------------------------+
| 1 | hello world of SQL |
| 2 | the QUICK-brown fox |
| 3 | modern-day DATA science |
| 4 | web-based FRONT-end development |
±-----------±--------------------------------+
输出：

对于 content_id = 1：
每个单词的首字母都是大写的：“Hello World Of Sql”
对于 content_id = 2：
包含的连字符词 “QUICK-brown” 变为 “Quick-Brown”
其它单词遵循普通的首字母大写规则
对于 content_id = 3：
连字符词 “modern-day” 变为 “Modern-Day”
“DATA” 转换为 “Data”
对于 content_id = 4：
包含两个连字符词：“web-based” → “Web-Based”
以及 “FRONT-end” → “Front-End”

题解

要考虑怎么把首字母切分出来，然后连接
substring，concat，lower、upper，然后就慢慢转化呗
技术上不好处理，可以把问题前置，比如用代码处理，不用sql

方法一

-- 文本处理函数集合：将文本转换为首字母大写格式（支持含短横线的单词）
SELECT
content_id,
content_text AS original_text,
(-- 主查询：处理每个单词并重新连接成文本SELECT GROUP_CONCAT(CASE-- 处理含短横线的复合词（如 "quick-brown" → "Quick-Brown"）WHEN INSTR(word, '-') > 0 THENCONCAT(UPPER(LEFT(SUBSTRING_INDEX(word, '-', 1), 1)), -- 第一部分首字母大写LOWER(SUBSTRING(SUBSTRING_INDEX(word, '-', 1), 2)), -- 其余小写'-', -- 保留短横线UPPER(LEFT(SUBSTRING_INDEX(word, '-', -1), 1)), -- 第二部分首字母大写LOWER(SUBSTRING(SUBSTRING_INDEX(word, '-', -1), 2)) -- 其余小写)-- 处理普通单词（如 "the" → "The"）ELSECONCAT(UPPER(LEFT(word, 1)), LOWER(SUBSTRING(word, 2)))ENDSEPARATOR ' ') -- 用空格连接处理后的单词FROM (-- 子查询：将文本按空格拆分为单词（最多支持6个单词，可扩展）SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(LOWER(content_text), ' ', n), ' ', -1)) AS wordFROM (SELECT content_text FROM user_content WHERE content_id = t.content_id) AS cJOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALLSELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 -- 扩展此范围以支持更多单词) AS numsON CHAR_LENGTH(LOWER(content_text)) - CHAR_LENGTH(REPLACE(LOWER(content_text), ' ', '')) >= nums.n - 1) AS words
) AS converted_text
FROM user_content t;

方法二

WITH RECURSIVE cte_split AS (-- 初始分割文本，将每个单词逐一提取SELECTcontent_id,@x := content_text AS original_text,@dash := LOCATE('-', @x) AS first_dash,@space := LOCATE(' ', @x) AS fist_space,@sec := CASEWHEN @dash <> '0' AND @space <> '0' THENLEAST(CAST(@space AS UNSIGNED), CAST(@dash AS UNSIGNED))WHEN @dash = '0' AND @space = '0' THENLENGTH(@x)ELSEGREATEST(CAST(@space AS UNSIGNED), CAST(@dash AS UNSIGNED))END AS separate_loc,LEFT(@x, CAST(@sec AS UNSIGNED)) AS word,SUBSTRING(@x, CAST(@sec AS UNSIGNED) + 1) AS remaining_text,0 AS word_order -- 跟踪单词的顺序FROM user_contentUNION ALL-- 递归分割剩余文本中的单词SELECTcontent_id,original_text,@dash := LOCATE('-', remaining_text) AS first_dash,@space := LOCATE(' ', remaining_text) AS fist_space,@sec := CASEWHEN @dash <> '0' AND @space <> '0' THENLEAST(CAST(@space AS UNSIGNED), CAST(@dash AS UNSIGNED))WHEN @dash = '0' AND @space = '0' THENLENGTH(remaining_text)ELSEGREATEST(CAST(@space AS UNSIGNED), CAST(@dash AS UNSIGNED))END AS separate_loc,LEFT(remaining_text, CAST(@sec AS UNSIGNED)) AS word,SUBSTRING(remaining_text, CAST(@sec AS UNSIGNED) + 1) AS remaining_text,word_order + 1 -- 跟踪单词的顺序FROM cte_splitWHERE remaining_text <> ''
),
cte_transformed AS (SELECTcontent_id,original_text,word_order,CONCAT(UPPER(SUBSTRING(word, 1, 1)),LOWER(SUBSTRING(word, 2)))AS transformed_wordFROM cte_split
),
cte_reconstructed AS (-- 重建文本，将转换后的单词重新组合SELECTcontent_id,original_text,GROUP_CONCAT(transformed_word ORDER BY word_order SEPARATOR '') AS converted_textFROM cte_transformedGROUP BY content_id
)
SELECTcontent_id,original_text,converted_text
FROM cte_reconstructed;