29 May 2019 · Ryan-Dallas ·       Add to Favorites   Report

How to capitalize first letter of word in BigQuery?

I need to capitalize a string:

john doe -> John Doe

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

#standardSQL
CREATE TEMPORARY FUNCTION capitalize(str STRING)
RETURNS STRING
LANGUAGE js AS """
  return str.replace(
      /\\w\\S*/g,
      function(txt) {
          return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();
      }
  );
""";
SELECT str, capitalize(str) capitalized_str
FROM `project.dataste.table`  

or

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

you can test above with dummy data as

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

with result for both options:

Row str         capitalized_str  
1   john doe    John Doe     
2   abc xyz     Abc Xyz    

Ryan-Dallas

posted on 29 May 2019

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied