How to capitalize first letter of word in BigQuery?

I need to capitalize a string:

\n\n

john doe -> John Doe

\n\n

Below is for BigQuery Standard SQL and with use of JS UDF (first query) and SQL UDF (second one)

\n\n
#standardSQL\nCREATE TEMPORARY FUNCTION capitalize(str STRING)\nRETURNS STRING\nLANGUAGE js AS """\n  return str.replace(\n      /\\w\\S*/g,\n      function(txt) {\n          return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();\n      }\n  );\n""";\nSELECT str, capitalize(str) capitalized_str\nFROM `project.dataste.table`  \n
\n\n

or

\n\n
#standardSQL\nCREATE TEMPORARY FUNCTION capitalize(str STRING) AS ((\n  SELECT STRING_AGG(CONCAT(UPPER(SUBSTR(word, 1, 1)), LOWER(SUBSTR(word, 2))), ' ' ORDER BY pos)\n  FROM UNNEST(SPLIT(str, ' ')) word WITH OFFSET pos\n));\nSELECT str, capitalize(str) capitalized_str\nFROM `project.dataste.table`\n
\n\n

you can test above with dummy data as

\n\n
WITH `project.dataste.table` AS (\n  SELECT 'john doe' AS str UNION ALL\n  SELECT 'abc xyz'\n)\n
\n\n

with result for both options:

\n\n
Row str         capitalized_str  \n1   john doe    John Doe     \n2   abc xyz     Abc Xyz    

Ryan-Dallas

posted on

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds