Proper case for BigQuery

[jonf]

I was struggling trying to find a way to capitalize names in Looker with data coming from BigQuery, since BigQuery lacks a PROPER() function – only LOWER() and UPPER() are available.

Searched in some forums and pretty much all solutions involved a User Defined Function in BigQuery, which (as far as I know) is not supported in Looker.

So, I came up with this subquery to properly capitalize strings:

WITH test as (
  SELECT 1 as AuthorId, "john smith" as Author
  UNION ALL
  SELECT 2, "jane doe"
)
SELECT  
AuthorId,
(
  SELECT 
    STRING_AGG(
      CONCAT(
        UPPER(
          SUBSTR(authors, 1, 1)
        ), 
        LOWER(
          SUBSTR(authors,2)
        )
      ), ' ' ORDER BY pos
    ) 
  FROM 
    UNNEST(SPLIT(Author, " ")) authors  WITH OFFSET pos
)
FROM test