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 19Enjoy 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
Post Comment