需要注意的是,这不是一个银弹,我提交了这个问题并提供了我自己的答案,因为经过大学的合理努力,我发现自己没有PostgreSQL的解决方案。
在我的案例中,任务是将一组基本上只使用大写字母的句子转换成一个合理的段落,每个句子的第一个字母都使用大写字母。如果这个解决方案已经存在,要么我是盲人,要么它隐藏得很好。
例如,如何转换
THE NAME IS BOND. JAMES BOND. 007. AND THIS IS ONE COOL PIECE OF CODE.
至
The name is bond. James bond. 007. And this is one cool piece of code.
以下是我的想法。我很乐意将答案授予一个更好的解决方案!
WITH fixed_sentences_source AS (
WITH single_sentences_source AS (
WITH arrays_source AS (
SELECT
regexp_split_to_array(LOWER('THE NAME IS BOND. JAMES BOND. 007. AND THIS IS ONE COOL PIECE OF CODE.'), '. ' ) AS arrays
)
SELECT TRIM(UNNEST(arrays)) AS single_sentences
FROM arrays_source
)
SELECT
UPPER(SUBSTRING(single_sentences, 1, 1)) || SUBSTRING(single_sentences, 2, LENGTH(single_sentences) - 1) AS fixed_sentence
FROM single_sentences_source
WHERE
single_sentences <> ''
)
SELECT ARRAY_TO_STRING(ARRAY(SELECT fixed_sentence FROM fixed_sentences_source), '. ')