String functions

Spanner supports the following MySQL string functions.You need to implement the MySQL functions in yourSpanner database before you can use them. For more information oninstalling the functions, seeInstall MySQL functions.

Function list

NameSummary
mysql.BIT_LENGTHReturns the length of a string in bits.
mysql.CHARInterprets the input parameter as an integer and returns a byte string consisting of the character given by the code value of that integer.
mysql.CONCAT_WSConcatenates two string with a specified separator string.
mysql.HEXReturns the hexadecimal representation of a string.
mysql.INSERTInserts a substring into a string at a specified position, replacing a specified number of characters.
mysql.LOCATEReturns the position of the first occurrence of substring.
mysql.MIDAlias forSUBSTRING.
mysql.OCTReturns a string containing an octal representation of the number.
mysql.ORDReturns the ASCII code of the leftmost character in a string.
mysql.POSITIONAlias formysql.LOCATE.
mysql.QUOTESanitize a string for use in a SQL statement.
mysql.REGEXP_LIKEReturns whether the string matches a regular expression.
mysql.REGEXP_SUBSTRReturns the first substring that matches a regular expression pattern.
mysql.SPACEReturns a string of the specified number of spaces.
mysql.STRCMPCompares two strings for equality.
mysql.SUBSTRING_INDEXReturns a substring from before or after a specified number of delimiter occurrences.
mysql.UNHEXConverts a string of hexadecimal characters into its byte equivalent.

mysql.BIT_LENGTH

mysql.BIT_LENGTH(string_expression)

Description

Returns the length of a given string in bits.

This function supports the following argument:

  • string_expression: The inputSTRING value.

Return data type

INT64

Example

The following example returns the bit length of the string 'google':

SELECTmysql.BIT_LENGTH('google')asbit_len;/*+---------+| bit_len |+---------+| 48      |+---------+*/

mysql.CHAR

mysql.CHAR(numeric_expression)

Description

Interprets an integer argument as a code value and returns aBYTES stringconsisting of the character for that code value. Arguments larger than 255 areconverted into multiple result bytes. This function emulates MySQL'smulti-argument behavior for values by taking the input modulo 4294967296 and theninterpreting the resulting integer's bytes. For example,CHAR(256) isequivalent toCHAR(1,0).

This function supports the following argument:

  • numeric_expression: TheINT64 value to convert to a byte character.

Return data type

BYTES

Differences from MySQL

This function does not support theUSING clause that MySQL'sCHAR() functionoffers for specifying character sets. It also only accepts a single integerargument, whereas MySQL'sCHAR() can accept multiple integer arguments to producea multi-character string.

Limitations

This function only handles a single integer argument and does not support theUSING clause.

Example

The following example returns the byte string for the character code 65:

SELECTmysql.CHAR(65)ASchar_from_code;/*+----------------+| char_from_code |+----------------+| A              |+----------------+*/

mysql.CONCAT_WS

mysql.CONCAT_WS(separator,value1,value2)

Description

Concatenates two strings with a specified separator string.

This function supports the following argument:

  • separator: TheSTRING to use as a separator.
  • value1: The firstSTRING.
  • value2: The secondSTRING.

Return data type

STRING

Differences from MySQL

Similar toCONCAT, MySQL converts arguments to strings if they are not already,but this implementation expectsSTRING arguments.

Limitations

This function only supports concatenating two strings with a separator. MySQL'sCONCAT_WS function can take a variable number of string arguments.

Example

The following example concatenates two strings using a hyphen as a separator:

SELECTmysql.CONCAT_WS('-','google','cloud')asconcatenated_string;/*+---------------------+| concatenated_string |+---------------------+| google-cloud        |+---------------------+*/

mysql.HEX

mysql.HEX(string_expression)

Description

Returns the hexadecimal representation of a string.

This function supports the following arguments:

  • string_expression: The inputSTRING.

Return data type

STRING

Differences from MySQL

Only theSTRING input version is provided. MySQL'sHEX() function can alsoaccept numeric arguments.

Limitations

This function only handlesSTRING input and does not support numeric input.

Example

The following example returns the hexadecimal representation of the string "SQL":

SELECTmysql.HEX('SQL')AShex_string;/*+------------+| hex_string |+------------+| 53514C     |+------------+*/

mysql.INSERT

mysql.INSERT(original_value,position,length,new_value)

Description

Inserts a substring into a string at a specified position, replacing a specifiednumber of characters.

This function supports the following arguments:

  • original_value: The originalSTRING.
  • position: The starting position for insertion (1-based). Ifpos is outside thelength ofstr, the original string is returned.
  • length: The number of characters in the original string to replace.
  • new_value: TheSTRING to insert.

Return data type

STRING

Limitations

INSERT is a reserved keyword. If you use this function in Data DefinitionLanguage (DDL) statements, such as in generated column definitions, you mustenclose the function name in backticks (for example,mysql.`INSERT`).

Example

The following example inserts "Google" into "Hello World" at position 7,replacing 0 characters:

SELECTmysql.`INSERT`('Hello World',7,0,'Google ')asinserted_string;/*+------------------------+| inserted_string        |+------------------------+| Hello Google World     |+------------------------+*/

mysql.LOCATE

mysql.LOCATE(substring,string)

Description

Returns the starting position (1-based) of the first occurrence of a substringwithin a string. The search is case-insensitive. Returns 0 if the substring isnot found.

This function supports the following arguments:

  • substring: TheSTRING to search for.
  • string: TheSTRING to be searched.

Return data type

INT64

Differences from MySQL

MySQL'sLOCATE() function also has a three-argument version that allowsspecifying a starting position for the search. This function only supports thetwo-argument version.

Limitations

This function does not support the three-argument version of MySQL'sLOCATE()(which includes a starting position).

Example

The following example finds the position of "Cloud" in "Google Cloud":

SELECTmysql.LOCATE('Cloud','Google Cloud')asposition_val;/*+--------------+| position_val |+--------------+| 8            |+--------------+*/

mysql.MID

mysql.MID(value,position,length)

Description

Alias forSUBSTRING.For more information, see theSUBSTRING entry.

mysql.OCT

mysql.OCT(numeric_expression)

Description

Returns a string containing the octal (base-8) representation of a number.

This function supports the following argument:

  • numeric_expression: The inputINT64 number.

Return data type

STRING

Example

The following example returns the octal representation of the number 10:

SELECTmysql.OCT(10)asoctal_value;/*+-------------+| octal_value |+-------------+| 12          |+-------------+*/

mysql.ORD

mysql.ORD(string_expression)

Description

Returns the numeric code of the leftmost character in a string. If the string isempty, the function returns the ASCII null character0.

This function supports the following argument:

  • string_expression: The inputSTRING.

Return data type

INT64

Example

The following example returns the character code for 'G':

SELECTmysql.ORD('Google')aschar_code;/*+-----------+| char_code |+-----------+| 71        |+-----------+*/

mysql.POSITION

mysql.POSITION(substring,string)

Description

Alias forLOCATE. For more information, see theLOCATE entry.

mysql.QUOTE

mysql.QUOTE(string_expression)

Description

Escapes a string for safe use as a string literal in a SQL statement byenclosing it in quotes and escaping special characters within the string.

This function supports the following argument:

  • string_expression: TheSTRING to quote. If the input isNULL,the result isNULL.

Return data type

STRING

Differences from MySQL

This function encloses the string in double quotes ("), while MySQL typicallyuses single quotes (') for this purpose. Both forms generally result in validSQL string literals.

Example

The following example quotes a string containing a single quote and backslash:

SELECTmysql.QUOTE("Don't\do it!")asquoted_string;/*+------------------------+| quoted_string          |+------------------------+| "Don't \\do it!"       |+------------------------+*/

mysql.REGEXP_LIKE

mysql.REGEXP_LIKE(string_expression,regular_expression[,match_type])

Description

Checks if a string matches a regular expression pattern.

This function supports the following arguments:

  • string_expression: The inputSTRING.
  • regular_expression: The regular expressionSTRING pattern.
  • match_type (Optional): ASTRING specifying the match behavior. Defaultsto'i' (case-insensitive). Supported values:
    • 'i': Case-insensitive matching.
    • 'c': Case-sensitive matching.
    • 'u' or'mu' or'um': Multi-line mode (lines split by\n), case-insensitive.
    • 'un' or'nu': The. character matches newlines, case-insensitive.

Return data type

BOOL

Limitations

  • Thematch_type 'm' (multiline supporting any Unicode line-separatingcharacter) is not supported.
  • Except as listed for'u' and'un' (and their permutations), different matchtypes cannot be combined by concatenating their characters.

Example

The following example checks if the string "New day" starts with "new"case-insensitively:

SELECTmysql.REGEXP_LIKE('New day','^new','i')asis_match;/*+----------+| is_match |+----------+| true     |+----------+*/

mysql.REGEXP_SUBSTR

mysql.REGEXP_SUBSTR(string_expression,regular_expression)

Description

Returns the substring that matches a regular expression pattern within an inputstring.

This function supports the following arguments:

  • string_expression: The inputSTRING.
  • regular_expression: The regular expressionSTRING pattern.

Return data type

STRING

Differences from MySQL

This function uses an underlying regular expression engine based on the re2library, which may have minor differences in behavior compared to MySQL'sregular expression implementation.

Limitations

This function does not support the optionalpos (position),occurrence, andmatch_type arguments that MySQL'sREGEXP_SUBSTR supports. The matching isimplicitly case-insensitive and extracts the first occurrence.

Example

The following example extracts the first word starting with 'C' from a string:

SELECTmysql.REGEXP_SUBSTR('Google Cloud Platform','C\\w*')assubstring_match;/*+-----------------+| substring_match |+-----------------+| Cloud           |+-----------------+*/

mysql.SPACE

mysql.SPACE(numeric_expression)

Description

Returns a string consisting of a specified number of space characters.

This function supports the following argument:

  • numeric_expression: TheINT64 number of spaces to return. Ifnumeric_expression is less than 0, an emptystring is returned.

Return data type

STRING

Limitations

This function can produce a string of spaces up to approximately 1MB in size.Requesting a number of spaces that would exceed this limit may result in an error.

Example

The following example returns a string of 5 spaces:

SELECTCONCAT('Hello',mysql.SPACE(3),'World')asthree_spaces;/*+---------------+| three_spaces  |+---------------+| Hello   World |+---------------+*/

mysql.STRCMP

mysql.STRCMP(string_expression1,string_expression2)

Description

Compares two strings lexicographically.Returns 0 if the strings are identical, -1 if the first string is less than thesecond, and 1 if the first string is greater than the second. ReturnsNULL ifeither string isNULL.

This function supports the following arguments:

  • string_expression1: The firstSTRING to compare.
  • string_expression2: The secondSTRING to compare.

Return data type

INT64

Differences from MySQL

MySQL'sSTRCMP supports comparison of both string and binary types.

Limitations

This function only supportsSTRING type inputs.

Example

The following example compares "apple" and "banana":

SELECTmysql.STRCMP('apple','banana')ascomparison_result;/*+-------------------+| comparison_result |+-------------------+| -1                |+-------------------+*/

mysql.SUBSTRING_INDEX

mysql.SUBSTRING_INDEX(string_expression,delimiter,count)

Description

Returns a substring from a string before or after a specified number of occurrencesof a delimiter. The match for the delimiter is case-sensitive.

This function supports the following arguments:

  • string_expression: The inputSTRING.
  • delimiter: The delimiterSTRING. Ifdelimiter is an empty string, the functionreturns an empty string.
  • count: AnINT64 specifying the number of occurrences ofdelimiter.Ifcount is positive, everything to the left of the final delimiter(counting from the left) is returned.Ifcount is negative, everything to the right of the final delimiter(counting from the right) is returned.

Return data type

STRING

Example

The following example extracts parts of a string using different counts:

SELECTmysql.SUBSTRING_INDEX('[www.google.com](https://www.google.com)','.',2)aspart1,mysql.SUBSTRING_INDEX('[www.google.com](https://www.google.com)','.',-2)aspart2;/*+--------------+-------------+| part1        | part2       |+--------------+-------------+| www.google   | google.com  |+--------------+-------------+*/

mysql.UNHEX

mysql.UNHEX(string_expression)

Description

Converts a string containing a hexadecimal representation of characters back tothe original characters (asBYTES). Each pair of hexadecimal digits in the inputstring is interpreted as a number, which is then converted to its characterequivalent.

This function supports the following argument:

  • string_expression: The inputSTRING representing a hexadecimal number.

Return data type

BYTES

Limitations

If the input string contains any non-hexadecimal characters, the behavior mightresult in an error or partial conversion, depending on the underlyingFROM_HEXimplementation.

Example

The following example converts the hexadecimal string "53514C" back to characters:

SELECTmysql.UNHEX('53514C')asoriginal_bytes;/*+----------------+| original_bytes |+----------------+| SQL            |+----------------+*/

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.