Google Cloud Spanner String Functions are used for various string manipulations in your SQL queries. Cloud Spanner supports most of the standard SQL string functions along with the many extensions to those functions.
In my other article, Google Cloud Spanner Regular Expression Functions, we have seen regular expression string functions. In this article, we will check other string functions.
Google Cloud Spanner String Functions
Following are some of the commonly used Cloud Spanner string functions.
| String Functions | Descriptions |
| BYTE_LENGTH(value) | Returns the length of the value in bytes. Value can be string or bytes. |
| CHAR_LENGTH(value) / CHARACTER_LENGTH(value) | Returns the length of the STRING in characters. |
| LENGTH(value) | Returns the length of the string. |
| CODE_POINTS_TO_BYTES (ascii_values) | Takes an array of extended ASCII code points (ARRAY of INT64) and returns BYTES. |
| TO_CODE_POINTS (value) | Returns an array of INT64. |
| CONCAT(value1[, …]) | Concatenates one or more STRING or BYTE values into a single string. |
| STARTS_WITH (value1, value2) | Returns TRUE if the second value is a prefix of the first. |
| ENDS_WITH(value1, value2) | Returns TRUE if the second value is a suffix of the first. |
| FORMAT (<format_string>, …) | Spanner SQL supports a FORMAT() function for formatting strings. It is similar to C printf function. |
| TO_BASE64 (bytes_expr) | Converts a sequence of BYTES into a base64-encoded STRING. |
| FROM_BASE64 (string_expr) | Converts the base64-encoded input string_expr into BYTES format. |
| TO_HEX(bytes) | Converts a sequence of BYTES into a hexadecimal STRING. |
| FROM_HEX(string) | Converts Hexadecimal string to bytes. |
| LPAD(original_value, return_length[, pattern]) | Returns a string that consists of original_value prepended with pattern. |
| RPAD(original_value, return_length[, pattern]) | Returns a value that consists of original_value appended with pattern. |
| LOWER(value) | Returns lower case string. |
| UPPER (value) | Returns UPPER case string. |
| TRIM (value1[, value2]) | Removes all leading and trailing characters that match value2. |
| LTRIM(value1[, value2]) | Function removes leading characters. |
| RTRIM(value1[, value2]) | Removes trailing characters. |
| REPLACE(original_value, from_value, to_value) | Replaces all occurrences of from_value with to_value in original_value. |
| REPEAT(original_value, repetitions) | Returns repeated original string. |
| REVERSE(value) | Returns the reverse of the input STRING or BYTES. |
| SAFE_CONVERT_BYTES_TO_STRING (value) | Converts a sequence of bytes to a string. Replaces UTF-8 characters with the Unicode replacement character, U+FFFD. |
| SPLIT(value[, delimiter]) | Splits input string on delimiter. |
| STRPOS (string, substring) | Returns the index of the first occurrence of substring inside string. Returns 0 if not found. |
| SUBSTR (value, position[, length]) | Returns a substring of the supplied position and length. |
Google Cloud Spanner String Functions Example
Following are the some of string function examples
Cloud Spanner pad zero using String function
select LPAD('1234',8,'0') as LPAD;
LPAD
00001234select RPAD('1234',8,'0') as RPAD;
RPAD
12340000Cloud Spanner SUBSTR function Examples
select SUBSTR ('1234789464361',1,5) as str;
str
12347Cloud Spanner REPLACE function Examples
select REPLACE ('123CDR123','CDR','123') as replace;
replace
123123123Cloud Spanner TRIM function Examples
select TRIM (' 1234567891 ') as trim;
trim
1234567891Hope this helps 🙂