How to capitalize first letter of word in BigQuery?
I need to capitalize a string:
\n\njohn doe -> John Doe
\n\nBelow 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\nor
\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\nyou can test above with dummy data as
\n\nWITH `project.dataste.table` AS (\n SELECT 'john doe' AS str UNION ALL\n SELECT 'abc xyz'\n)\n
\n\nwith result for both options:
\n\nRow str capitalized_str \n1 john doe John Doe \n2 abc xyz Abc Xyz
Ryan-Dallas
posted onEnjoy 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