How to aggregate back a split word in big query-CodePudding

I am trying to aggregate a split word back together. The reason I split the word in the first place because I want to capitalize to all the first letter. For example, dog corki would return Dog Corki in capital. I have the following code, but the offset function does not help to align all the word back. It returns the following

 Corki Dog Golden
 Corki Retriver

However, I want it to return the following

Corki
Dog Corki
Golden Retreiver

Here is the code:

WITH array_table AS (
  SELECT "corki" AS dog UNION ALL
  SELECT "dog corki" UNION ALL
  SELECT "golden retriever"
)
,

split_table AS (
SELECT SPLIT(array_table.dog, " ") AS split_word
FROM array_table
)

SELECT 
  -- split_table.split_word,
  -- unnest_split_word,
  -- pos
  STRING_AGG(CONCAT(UPPER(SUBSTR(unnest_split_word, 1, 1)), LOWER(SUBSTR(unnest_split_word, 2))), ' ')
FROM 
split_table,
UNNEST(split_table.split_word) AS unnest_split_word
WITH OFFSET AS pos
GROUP BY pos

Thank you in advance!

CodePudding user response：

You can use INITCAP function instead.

WITH array_table AS (
  SELECT "corki" AS dog UNION ALL
  SELECT "dog corki" UNION ALL
  SELECT "golden retriever"
)
SELECT INITCAP(dog) FROM array_table;

 ------------------ 
|       f0_        |
 ------------------ 
| Corki            |
| Dog Corki        |
| Golden Retriever |
 ------------------

And if we use your approch, below will return same result as above.

WITH array_table AS (
  SELECT "corki" AS dog UNION ALL
  SELECT "dog corki" UNION ALL
  SELECT "golden retriever"
),
split_table AS (
  SELECT FORMAT('%t', t) AS hash_id, *
    FROM array_table t, UNNEST(SPLIT(dog, " ")) AS split_word WITH offset
)
SELECT STRING_AGG(INITCAP(split_word), ' ' ORDER BY offset)
  FROM split_table
 GROUP BY hash_id;