如果你不想编写自己的存储函数,可以使用JSON_TABLE将文本“拆分”成多行(每个单词一行),然后对每个单词进行处理并将它们拼接在一起。
CREATE TEMPORARY TABLE tmp_names AS
VALUES ROW(1, "ted theodore LOGAN")
, ROW(2, "BiLL S preston esquire")
, ROW(3, "rufus")
;
CREATE TEMPORARY TABLE tmp_names2 AS SELECT * FROM tmp_names;
SELECT *
FROM tmp_names AS t
INNER JOIN JSON_TABLE(
CONCAT('["', REPLACE(t.column_1, ' ', '","'), '"]')
, "$[*]" COLUMNS (name VARCHAR(100) PATH "$")
) AS s
;
UPDATE tmp_names AS t
INNER JOIN (
SELECT t.column_0, t.column_1
, GROUP_CONCAT(
CONCAT(
UPPER(SUBSTR(s.name, 1, 1))
, LOWER(SUBSTR(s.name, 2))
)
SEPARATOR ' '
) AS title_cased
FROM tmp_names2 AS t
INNER JOIN JSON_TABLE(
CONCAT('["', REPLACE(t.column_1, ' ', '","'), '"]')
, "$[*]" COLUMNS (name VARCHAR(100) PATH "$")
) AS s
GROUP BY 1, 2
) AS j
ON j.column_0 = t.column_0
SET t.column_1 = j.title_cased
;
SELECT * FROM tmp_names;