25 String Functions in BigQuery that You Must Know

String functions in BigQuery allow you to manipulate the data that you retrieve with your SQL queries.

In this post, I am introducing 10+ string functions that you must know to excel in BigQuery. In future posts, I will share specific use cases for these functions.

1. CONCACT(value 1, value 2, value 3, ….) - This function allows you to combine various values into a single value. For example, if you have first name and last name in separate fields but you need the full name of the person then you can use this function to combine those values.

   Example: SELECT CONCAT(“Anil”, “ “, “Batra”) will return ‘Anil Batra’

2. LOWER(value)- This function turns all the characters of the specified value into lowercase.

   Example: SELECT LOWER(“OPTIZENT”) will return ‘optizent’

3. UPPER(value) - This function turns all the characters of the specified value into uppercase.

   Example: SELECT UPPER(“optizent’) will return ‘OPTIZENT’

4. CHAR_LENGTH(value) - This function returns the number of characters in the specified value.

   Example: SELECT CHAR_LENGTH(“OPTIZENT”) will return 8

5. CHARCATER_LENGTH(value) - Similar to CHAR_LENGTH, this function returns the number of characters in the specified value.

   Example: SELECT CHARACTER_LENGTH(“OPTIZENT”) will return 8

6. LENGTH(value) - Similar to CHAR_LENGTH, this function returns the number of characters in the specified value.

7. SUBSTR(value, position, [length]) - this function returns a portion of the specified value starting from the position that is specified in the function to the end of the string or number of characters specified in the length parameter, which is optional.

   Example: SELECT SUBSTRING(“Anil Batra”, 6) will return ‘Batra’

   SELECT SUBSTRING(“Anil Batra”, 6, 3) will return ‘Bat’

8. SUBSTRING(value, position, [length])) - This is an alias of SUBSTR and works just like it.

9. SPLIT(value, [delimiter]) - This function splits the specified value, containing multiple values separated by a delimiter, into an array of individual values. The delimiter is optional and the default delimiter is a comma.

   Example: SELECT SPLIT(“Anil, Batra, Optizent”) will return an array as [“Anil”, “Batra”, “Optizent”]

10. TRIM(value, [set of characters to be removed]) - This function removes the specified set of characters from the beginning and end of the value passed. The set of characters is optional and if nothing is specified then whitespace characters are removed. The values passed in the function are case-sensitive.

    Example: SELECT TRIM(“ Anil Batra ”) will return “Anil Batra”

    SELECT TRIM(“ Anil Batra”, "a”) will return “Anil Batr”

11. RTRIM(value, [set of characters to be removed]) - Similar to TRIM but only the ending character (right-hand side) is removed.

    Example: SELECT RTRIM(“ Anil Batra”, "A”) will return “Anil Batra” since there is no lowercase “a” on the right-hand side

12. LTRIM(value, [set of characters to be removed]) - Similar to TRIM but only the leading character (left-hand side) is removed.

    Example: SELECT LTRIM(“ Anil Batra”, "A”) will return “nil Batra”.

13. INITCAP(value, [delimiters]) - return the value by capitalizing the initial character of each value separated by the specified delimiters. If no delimiter is specified then whitespace is considered the delimiter.

    Example: SELECT INITCAP(“anil batra”) will return “Anil Batra”

14. LEFT(value, length) - Get a specified number of characters starting from the left of the value specified in the function.

    Example: SELECT LEFT("Optizent Analytics", 8 ) will return Optizent.

15. RIGHT(value, length) - Get the specified number of characters starting from the right of the value specified in the function.

    Example: SELECT RIGHT("Optizent Analytics", 9 ) will return Analytics.

16. LPAD(value, return length, [pattern to use for padding]) - This function returns the number of characters specified in return length by adding the pattern to the specified value.

    Example: SELECT LPAD("Optizent Analytics", 20, "@") will return “@@Optizent Analytics”. Since the length of the input value (Optizent Analytics) is 18, the function added two @ symbols to make it a length of 20.

17. RPAD(value, return length, [pattern to use for padding]) - Same as LPAD but adds the pattern at the end.

    Example: SELECT RPAD("Optizent Analytics", 20, "@") will return “Optizent Analytics@@”. Since the length of the input value (Optizent Analytics) is 18, the function added two @ symbols to make it a length of 20.

In the above examples, I took a static string value to illustrate how these functions work but you can use these functions on any column in SQL commands.

Stay tuned for many such lessons.

Thank you,
Anil Batra, Optizent.com