Welcome to Subscribe On Youtube

3368. First Letter Capitalization 🔒

Description

Table: user_content

+-++
\| content_id  \| int     \|
\| content_text\| varchar \|
+-+--+
\| content_id \| content_text                      \|
++--+

Output:

++--+
\| content_id \| original_text                     \| converted_text                    \|
++--+
\| 1          \| hello world of SQL                \| Hello World Of SQL                \|
\| 2          \| the QUICK brown fox               \| The Quick Brown Fox               \|
\| 3          \| data science AND machine learning \| Data Science And Machine Learning \|
\| 4          \| TOP rated programming BOOKS       \| Top Rated Programming Books       \|
++--+

Explanation:

  • For content_id = 1:
    • Each word's first letter is capitalized: Hello World Of SQL
  • For content_id = 2:
    • Original mixed-case text is transformed to title case: The Quick Brown Fox
  • For content_id = 3:
    • The word AND is converted to "And": "Data Science And Machine Learning"
  • For content_id = 4:
    • Handles word TOP rated correctly: Top Rated
    • Converts BOOKS from all caps to title case: Books

</div>

Solutions

Solution 1

  • import pandas as pd


def process_text(user_content: pd.DataFrame) -> pd.DataFrame:
    user_content["converted_text"] = user_content["content_text"].apply(
        lambda text: " ".join(word.capitalize() for word in text.split(" "))
    )
    return user_content[["content_id", "content_text", "converted_text"]].rename(
        columns={"content_text": "original_text"}
    )


  • WITH RECURSIVE
    capitalized_words AS (
        SELECT
            content_id,
            content_text,
            SUBSTRING_INDEX(content_text, ' ', 1) AS word,
            SUBSTRING(
                content_text,
                LENGTH(SUBSTRING_INDEX(content_text, ' ', 1)) + 2
            ) AS remaining_text,
            CONCAT(
                UPPER(LEFT(SUBSTRING_INDEX(content_text, ' ', 1), 1)),
                LOWER(SUBSTRING(SUBSTRING_INDEX(content_text, ' ', 1), 2))
            ) AS processed_word
        FROM user_content
        UNION ALL
        SELECT
            c.content_id,
            c.content_text,
            SUBSTRING_INDEX(c.remaining_text, ' ', 1),
            SUBSTRING(c.remaining_text, LENGTH(SUBSTRING_INDEX(c.remaining_text, ' ', 1)) + 2),
            CONCAT(
                c.processed_word,
                ' ',
                CONCAT(
                    UPPER(LEFT(SUBSTRING_INDEX(c.remaining_text, ' ', 1), 1)),
                    LOWER(SUBSTRING(SUBSTRING_INDEX(c.remaining_text, ' ', 1), 2))
                )
            )
        FROM capitalized_words c
        WHERE c.remaining_text != ''
    )
SELECT
    content_id,
    content_text AS original_text,
    MAX(processed_word) AS converted_text
FROM capitalized_words
GROUP BY 1, 2;

All Problems

All Solutions