Skip to content

3368. First Letter Capitalization

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
WITH RECURSIVE
  Words AS (
    -- Extract the first word and assign a token index.
    SELECT
      content_id,
      SUBSTRING_INDEX(content_text, ' ', 1) AS word,
      SUBSTRING(
        content_text,
        LENGTH(SUBSTRING_INDEX(content_text, ' ', 1)) + 2
      ) AS remaining_text,
      1 AS token_index
    FROM user_content
    UNION ALL
    -- Recursively extract the next word and increment the token index.
    SELECT
      content_id,
      SUBSTRING_INDEX(remaining_text, ' ', 1) AS word,
      SUBSTRING(
        remaining_text,
        LENGTH(SUBSTRING_INDEX(remaining_text, ' ', 1)) + 2
      ) AS remaining_text,
      token_index + 1 AS token_index
    FROM Words
    WHERE remaining_text != ''
  ),
  Converted AS (
    -- Combine the words back in original order with proper capitalization.
    SELECT
      content_id,
      GROUP_CONCAT(
        CONCAT(
          UPPER(SUBSTRING(word, 1, 1)),
          LOWER(SUBSTRING(word, 2))
        )
        ORDER BY token_index SEPARATOR ' '
      ) AS converted_text
    FROM Words
    GROUP BY 1
  )
SELECT
  UserContent.content_id,
  UserContent.content_text AS original_text,
  Converted.converted_text
FROM user_content AS UserContent
INNER JOIN Converted
  USING (content_id);