CODEWITHSUNDEEP
google-bigquery
Daniel Zrůst
Daniel Zrůst

Reputation: 211

Proper Case in Big Query

I have this sentence "i want to buy bananas" across column 'Bananas' in Big Query.

I want to get "I Want To Buy Bananas". How do I it? I was expecting PROPER(Bananas) function when I saw LOWER and UPPER but it seems like PROPER case is not supported?

DZ

Upvotes: 7

Views: 29855

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173003

October 2020 Update:

BigQuery now support INITCAP function - which takes a STRING and returns it with the first character in each word in uppercase and all other characters in lowercase. Non-alphabetic characters remain the same.

So, below type of fancy-shmancy UDF is not needed anymore - instead you just use

#standradSQL
SELECT str, INITCAP(str) proper_str
FROM `project.dataset.table`

-- ~~~~~~~~~~~~~~~~~~

Below example is for BigQuery Standrad SQL

#standradSQL
CREATE TEMP FUNCTION PROPER(str STRING) AS (( 
  SELECT STRING_AGG(CONCAT(UPPER(SUBSTR(w,1,1)), LOWER(SUBSTR(w,2))), ' ' ORDER BY pos) 
  FROM UNNEST(SPLIT(str, ' ')) w WITH OFFSET pos
));
WITH `project.dataset.table` AS (
  SELECT 'i Want to buy bananas' str
)
SELECT str, PROPER(str) proper_str
FROM `project.dataset.table`

result is

Row str                     proper_str   
1   i Want to buy bananas   I Want To Buy Bananas

Upvotes: 16

Mark M
Mark M

Reputation: 525

I expanded on Mikhail Berlyant's answer to also capitalise after hypens (-) as I needed to use proper case for place names. Had to switch from the SPLIT function to using a regex to do this.

I test for an empty string at the start and return an empty string (as opposed to null) to match the behaviour of the native UPPER and LOWER functions.

CREATE TEMP FUNCTION PROPER(str STRING) AS (( 
  SELECT 
    IF(str = '', '',
      STRING_AGG(
        CONCAT(
          UPPER(SUBSTR(single_words,1,1)), 
          LOWER(SUBSTR(single_words,2))
        ), 
        '' ORDER BY position
      )
    )
  FROM UNNEST(REGEXP_EXTRACT_ALL(str, r' +|-+|.[^ -]*')) AS single_words
  WITH OFFSET AS position
));

WITH test_table AS (
  SELECT 'i Want to buy bananas' AS str
  UNION ALL
  SELECT 'neWCASTle upon-tyne' AS str
)

SELECT str, PROPER(str) AS proper_str
FROM test_table

Output

Row str                     proper_str   
1   i Want to buy bananas   I Want To Buy Bananas  
2   neWCASTle upon-tyne     Newcastle Upon-Tyne

Upvotes: 1

Related Questions