【LeetCode 3368. 3374. 首字母大写】MySQL和T-SQL中实现和Oracle的NLS_INITCAP函数类似的效果
Problem: 3374. 首字母大写 II
题目地址
https://leetcode.cn/problems/first-letter-capitalization/description/ 3368. 首字母大写。更简单一点，会员题。
https://leetcode.cn/problems/first-letter-capitalization-ii/description/ 3374. 首字母大写 II。非会员题。
背景
Oracle有initcap和nls_initcap函数，可以直接过这题。
PostgreSQL有initcap函数，可以直接过这题。
MySQL和T-SQL中我没有找到可以直接过这题的对应函数。
代码
Mysql
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM numbers
WHERE n < 20 -- You can adjust this limit based on your needs
),
t1 AS (
SELECT
content_id,
content_text AS original_text,
(
SELECT GROUP_CONCAT(
CASE
WHEN LOCATE('-', word) > 0 THEN
CONCAT(
UPPER(SUBSTRING(SUBSTRING_INDEX(word, '-', 1), 1, 1)),
LOWER(SUBSTRING(SUBSTRING_INDEX(word, '-', 1), 2)),
'-',
UPPER(SUBSTRING(SUBSTRING_INDEX(word, '-', -1), 1, 1)),
LOWER(SUBSTRING(SUBSTRING_INDEX(word, '-', -1), 2))
)
ELSE
CONCAT(
UPPER(SUBSTRING(word, 1, 1)),
LOWER(SUBSTRING(word, 2))
)
END
ORDER BY pos
SEPARATOR ' '
)
FROM (
SELECT
n AS pos,
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(content_text), ' ', n), ' ', -1)) AS word
FROM numbers
WHERE n <= 1 + LENGTH(TRIM(content_text)) - LENGTH(REPLACE(TRIM(content_text), ' ', ''))
ORDER BY n
) words
) AS converted_text
FROM user_content
)
SELECT
content_id,
original_text,
converted_text
FROM t1;
t-sql
WITH numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM numbers
WHERE n < 20 -- Adjust this limit based on your needs
),
words AS (
SELECT
content_id,
content_text,
value AS word,
ROW_NUMBER() OVER (PARTITION BY content_id ORDER BY (SELECT NULL)) AS pos
FROM user_content
CROSS APPLY STRING_SPLIT(TRIM(content_text), ' ')
),
t1 AS (
SELECT
content_id,
content_text AS original_text,
STRING_AGG(
CASE
WHEN CHARINDEX('-', word) > 0 THEN
CONCAT(
UPPER(LEFT(LEFT(word, CHARINDEX('-', word) - 1), 1)),
LOWER(RIGHT(LEFT(word, CHARINDEX('-', word) - 1), LEN(LEFT(word, CHARINDEX('-', word) - 1)) - 1)),
'-',
UPPER(LEFT(RIGHT(word, LEN(word) - CHARINDEX('-', word)), 1)),
LOWER(RIGHT(RIGHT(word, LEN(word) - CHARINDEX('-', word)), LEN(RIGHT(word, LEN(word) - CHARINDEX('-', word))) - 1))
)
ELSE
CONCAT(
UPPER(LEFT(word, 1)),
LOWER(RIGHT(word, LEN(word) - 1))
)
END,
' '
) AS converted_text
FROM words
GROUP BY content_id, content_text
)
SELECT
content_id,
original_text,
converted_text
FROM t1
order by content_id asc
提示词
Using Claude 3.7 & Grok 3
点击展开
```
-- First, let's create a function to handle the capitalization logic
CREATE FUNCTION CapitalizeText(input_text VARCHAR(255))
RETURNS VARCHAR(255)
-- DETERMINISTIC
BEGIN
DECLARE result VARCHAR(255) DEFAULT '';
DECLARE word VARCHAR(100);
DECLARE subword VARCHAR(100);
DECLARE pos INT DEFAULT 1;
DECLARE len INT;
DECLARE has_hyphen INT;
-- Split by spaces
SET input_text = TRIM(input_text);
SET len = LENGTH(input_text);
WHILE pos <= len DO
-- Extract next word
SET word = SUBSTRING_INDEX(SUBSTRING(input_text, pos), ' ', 1);
SET pos = pos + LENGTH(word) + 1;
-- Check if word contains hyphen
IF LOCATE('-', word) > 0 THEN
-- Handle hyphenated words
SET subword = SUBSTRING_INDEX(word, '-', 1);
SET result = CONCAT(
result,
UPPER(LEFT(subword, 1)),
LOWER(RIGHT(subword, LENGTH(subword) - 1)),
'-',
UPPER(LEFT(RIGHT(word, LENGTH(word) - LENGTH(subword) - 1), 1)),
LOWER(RIGHT(word, LENGTH(word) - LENGTH(subword) - 2))
);
ELSE
-- Handle regular words
SET result = CONCAT(
result,
UPPER(LEFT(word, 1)),
LOWER(RIGHT(word, LENGTH(word) - 1))
);
END IF;
-- Add space if not at end
IF pos <= len THEN
SET result = CONCAT(result, ' ');
END IF;
END WHILE;
RETURN result;
END
select content_id , content_text as original_text,
CapitalizeText(content_text) as converted_text
from user_content
```
rewrite the mysql code using user-define variable (all in one), don't declare function.
先让Claude 3.7改写代码。
尽管Prompt里要求使用用户变量(prefixed with @)完成，实际最后多轮思考返回后的正确AC代码并没有使用用户变量。 （中间思考过程中使用用户变量的代码是错的）
之后尝试让Grok3拿recursive CTE重写一下AC代码。
```
with t1 as(
SELECT
content_id,
content_text AS original_text,
(
SELECT GROUP_CONCAT(
CASE
WHEN LOCATE('-', word) > 0 THEN
-- Handle hyphenated word
CONCAT(
UPPER(SUBSTRING(SUBSTRING_INDEX(word, '-', 1), 1, 1)),
LOWER(SUBSTRING(SUBSTRING_INDEX(word, '-', 1), 2)),
'-',
UPPER(SUBSTRING(SUBSTRING_INDEX(word, '-', -1), 1, 1)),
LOWER(SUBSTRING(SUBSTRING_INDEX(word, '-', -1), 2))
)
ELSE
-- Handle regular word
CONCAT(
UPPER(SUBSTRING(word, 1, 1)),
LOWER(SUBSTRING(word, 2))
)
END
ORDER BY pos
SEPARATOR ' '
)
FROM (
SELECT
n as pos,
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(content_text), ' ', n), ' ', -1)) AS word
FROM (
-- Generate a sequence of numbers to handle words
SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL
SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL
SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
) numbers
WHERE n <= 1 + LENGTH(TRIM(content_text)) - LENGTH(REPLACE(TRIM(content_text), ' ', ''))
ORDER BY n
) words
) AS converted_text
FROM user_content
)
select content_id,
original_text, converted_text
from t1
```
generating `numbers` process using recursive CTE to complete it.
浙公网安备 33010602011771号