SQL-leetcode—3374. 首字母大寫 II

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;

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/919524.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/919524.shtml
英文地址，請注明出處：http://en.pswp.cn/news/919524.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！