Case

In this section, we cover how to set the case of a string.

There are four common case settings:

  1. Lower where all characters are lower case e.g. "lower".
  2. Upper where all characters are upper case e.g. "Upper".
  3. Title where the first character of each word is upper case and the rest is lower case e.g. " String Formatting".
  4. Sentence where the first character of the first word is upper case and the rest is lower case e.g. "String formatting is cool".

To Lower

We wish to set the case of a string column to lower case.

In this example, we wish to set the case of all the individual string values in the string column col_1 to lower case.

SELECT col_1, LOWER(col_1) col_2
FROM table_1;

Here is how this works:

  • We use the LOWER() function to set the case of all letters in each string element in the column col_1 to lower case.
  • Most commonly, the LOWER() function takes one argument which is the string column (or individual string literal) whose case is to be set to lower case.
  • The output will have the same number of rows as the original table table_1 but with an additional column col_2 holding an all lower case version of the column col_1.

To Upper

We wish to set the case of a string column (or literal) to upper case.

In this example, we wish to set the case of all the individual string values in the string column col_1 to upper case.

SELECT col_1, UPPER(col_1) col_2
FROM table_1;

Here is how this works:

  • We use the UPPER() function to set the case of all letters in each string element in the column col_1 to upper case.
  • Most commonly, the LOWER() function takes one argument which is the string column (or individual string literal) whose case is to be set to upper case.
  • The output will have the same number of rows as the original table table_1 but with an additional column col_2 holding an all upper case version of the column col_1.

To Title

We wish to set the case of a string column (or literal) to title capitalization. Title capitalization is where the first character of each word is upper case and the rest is lower case e.g. "String Formatting".

In this example, we wish to set the case of all the individual string values in the string column col_1 to title case.

SELECT col_1, INITCAP(col_1) col_2
FROM table_1

Here is how this works:

  • We use the INITCAP() function to set the case of all letters in each string element in the column col_1 to title case.
  • Most commonly, the INITCAP() function takes one argument which is the string column (or individual string literal) whose case is to be set to title case.
  • The output will have the same number of rows as the original table table_1 but with an additional column col_2 holding an all title case version of the column col_1.

To Sentence

We wish to set the case of a string column (or literal) to sentence capitalization. Sentence capitalization is where the first character of the first word is upper case and the rest is lower case e.g. "String formatting is cool.". In this example, we wish to set the case of all the individual string values in the string column col_1 to sentence case.

SELECT col_1,
       CONCAT(UPPER(SUBSTR(col_1, 1, 1)),
              LOWER(SUBSTR(col_1, 2))) AS col_2
FROM table_1;

Here is how this works:

  • There is no builtin function in SQL to set the case of all letters in each string element in the column col_1 to sentence case.
  • We can do this by utilizing other builtin functions such as UPPER(), LOWER(), SUBSTR() , and CONCAT().
  • The CONCAT() function is used to concatenate, or join, multiple strings together. In this case, it is being used to create a new string by combining the following three strings:
    • UPPER(SUBSTR(col_1, 1, 1)): SUBSTR(col_1, 1, 1) is the first character of col_1, converted to uppercase using the UPPER function.
    • LOWER(SUBSTR(col_1, 2)): SUBSTR(col_1, 2) is the rest of col_1, starting from the second character and going to the end, converted to lowercase using the LOWER() function.
  • The output will have the same number of rows as the original table table_1 but with an additional column col_2 holding an all sentence case version of the column col_1.

Alternative: Using a user defined function

CREATE OR REPLACE FUNCTION dataset.SENTENCE(val STRING)
    RETURNS STRING
AS
    (
        CONCAT(UPPER(SUBSTR(val, 1, 1)),
               LOWER(SUBSTR(val, 2)))
    );


WITH table_1 AS (SELECT *
                 FROM UNNEST(['format a string',
                     'set to lower case',
                     'try Upper case']) AS col_1)

SELECT col_1, dataset.SENTENCE(col_1) col_2
FROM table_1;

Here is how this works:

  • We can create a UDF to convert a column to sentence case.
  • The function is called SENTENCE and takes a string as input and returns a modified version of the string. The function is defined using the CREATE OR REPLACE FUNCTION statement.
  • The function definition consists of the following parts:
    • dataset.SENTENCE(val STRING): This is the function signature, which specifies the name of the function (SENTENCE) and the input parameter (val) of type STRING. The dataset. prefix indicates that the function belongs to a dataset called dataset.
    • RETURNS STRING: This specifies the return type of the function, which is a string in this case.
    • (CONCAT(UPPER(SUBSTR(val, 1, 1)), LOWER(SUBSTR(val, 2)))): This is the body of the function, which defines the logic for transforming the input string. It works similarly to the solution provided above.
    • The SELECT statement then selects the col_1 column from table_1 and applies the SENTENCE function to each value in the column, creating a new column called col_2 that contains the modified strings.
SQL
I/O