Help Center/ GaussDB/ MySQL Compatibility(Distributed)/ MySQL Compatibility in MySQL-Compatible Mode/ System Functions/ String Functions
Updated on 2024-12-06 GMT+08:00
View PDF
Share

String Functions

Table 1 String functions

No.

MySQL

GaussDB

Difference

1

BIN()

Supported.

In GaussDB, the types supported by function input parameter 1 are as follows:

  • Integer types: tinyint, smallint, mediumint, int, and bigint.
  • Unsigned integer types: tinyint unsigned, smallint unsigned, int unsigned, and bigint unsigned.
  • Character and text types: char, varchar, tinytext, text, mediumtext, and longtext. Only numeric integer strings are supported, and the integer range is within the bigint range.
  • Floating-point types: float, real, and double.
  • Fixed-point types: numeric, decimal, and dec.
  • Boolean type: bool.

2

CONCAT()

Supported.

The data type of the return value of CONCAT is always text regardless of the data type of the parameter. However, in MySQL, if CONCAT contains binary parameters, the return value is binary.

3

CONCAT_WS()

Supported.

The data type of the return value of CONCAT_WS is always text regardless of the data type of the parameter. However, in MySQL, if CONCAT_WS contains binary parameters, the return value is binary. In other cases, the return value is a string.

4

ELT()

Supported.
  • In GaussDB, the types supported by function input parameter 1 are as follows:

    Integer types: tinyint, smallint, mediumint, int, and bigint.

    Unsigned integer types: tinyint unsigned, smallint unsigned, and int unsigned.

    Character and text types: char, varchar, tinytext, text, mediumtext, and longtext. Only numeric integer strings are supported, and the integer range is within the bigint range.

    Floating-point types: float, real, and double.

    Fixed-point types: numeric, decimal, and dec.

    Boolean type: bool.

  • In GaussDB, the types supported by function input parameter 2 are as follows:

    Integer types: tinyint, smallint, mediumint, int, and bigint.

    Unsigned integer types: tinyint unsigned, smallint unsigned, int unsigned, and bigint unsigned.

    Character and text types: char, varchar, tinytext, text, mediumtext, and longtext.

    Floating-point types: float, real, and double.

    Fixed-point types: numeric, decimal, and dec.

    Boolean type: bool.

    Large object types: tinyblob, blob, mediumblob, and longblob.

    Date types: datetime, timestamp, date, and time.

5

FIELD

Supported.

When function input parameters range from the maximum bigint value to the maximum bigint unsigned value, incompatibility occurs.

When function input parameters are of float(m, d), double(m, d), or real(m, d) type, the precision is higher and incompatibility occurs.

6

FIND_IN_SET()

Supported.

When the database encoding is set to 'SQL_ASCII', the default case sensitivity rule is not supported. That is, if no character set rule is specified, uppercase and lowercase letters are treated as distinct.

7

INSERT()

Supported.
  • The range of input parameters of the Int64 type is from –9223372036854775808 to +9223372036854775807. If a value is out of range, an error is reported. MySQL does not limit the range of input parameters of the numeric type. If an exception occurs, an alarm is generated, indicating that the value is set to the upper or lower limit.
  • The maximum length of the input parameter of the text type is 2^30 – 5 bytes, and the maximum length of the input parameter of the bytea type is 2^30 – 512 bytes.
  • If any of the s1 and s2 parameters is of the bytea type and the result contains invalid characters, the displayed result may be different from that of MySQL, but the character encoding is the same as that of MySQL.

8

LOCATE()

Supported.

When input parameter 1 is of the bytea type and input parameter 2 is of the text type, the behavior of GaussDB is different from that of MySQL.

9

MAKE_SET()

Supported.
  • When the bits parameter is an integer, the maximum range is int128, which is smaller than the MySQL range.
  • When the bits parameter is of the date type (datetime, timestamp, date, or time), it is not supported because the conversion from the date type to the integer type is different from that in MySQL.
  • GaussDB and MySQL are inherently different in the bit and Boolean types, causing different returned results. When the bits input parameter is of the Boolean type, and the str input parameter is of the bit or Boolean type, they are not supported.
  • When the bits input parameter is of the character string or text type, only the pure integer format is supported. In addition, the value range of pure integers is limited to bigint.
  • The integer value of the str input parameter exceeds the range from 81 negative nines to 81 positive nines. The return value is different from that of MySQL.
  • When the str input parameter is expressed in scientific notation, trailing zeros are displayed in GaussDB but not displayed in MySQL. This is an inherent difference.

10

QUOTE()

Supported.
  • If the str character string contains "\Z", "\r", "\%", or "\_", GaussDB does not escape it, which is different from MySQL. The slash followed by digits may also cause differences, for example, "\563". This function difference is the escape character difference between GaussDB and MySQL.
  • The output format of "\b" in the str character string is different from that in MySQL. This is an inherent difference between GaussDB and MySQL.
  • If the str character string contains "\0", GaussDB cannot identify the character because the UTF-8 character set cannot identify the character. As a result, the input fails. This is an inherent difference between GaussDB and MySQL.
  • If str is of the bit or Boolean type, this type is not supported because it is different in GaussDB and MySQL.
  • GaussDB supports a maximum of 1 GB data transfer. The maximum length of the str input parameter is 536870908, and the maximum size of the result string returned by the function is 1 GB.
  • The integer value of the str input parameter exceeds the range from 81 negative nines to 81 positive nines. The return value is different from that of MySQL.
  • When the str input parameter is expressed in scientific notation, trailing zeros are displayed in GaussDB but not displayed in MySQL. This is an inherent difference.

11

SPACE()

Supported.
  1. The maximum value of GaussDB input parameter 1 is 1073741818. If the value exceeds 1073741818, an empty string is returned. By default, the maximum value of MySQL input parameter 1 is 4194304. If the value exceeds 4194304, an alarm is generated.
  2. In GaussDB, the types supported by function input parameter 1 are as follows:
    • Integer types: tinyint, smallint, mediumint, int, and bigint.
    • Unsigned integer types: tinyint unsigned, smallint unsigned, and int unsigned.
    • Character and text types: char, varchar, tinytext, text, mediumtext, and longtext. Only numeric integer strings are supported, and the integer range is within the bigint range.
    • Floating-point types: float, real, and double.
    • Fixed-point types: numeric, decimal, and dec.
    • Boolean type: bool.

12

SUBSTR()

Supported.

-

13

SUBSTRING()

Supported.

-

14

SUBSTRING_INDEX()

Supported.

-

15

STRCMP()

Supported.
  1. Different from MySQL, GaussDB supports the following input parameter types:
    • Character types: CHAR, VARCHAR, NVARCHAR2, and TEXT.
    • Binary type: BYTEA.
    • Numeral types: TINYING [UNSIGNED], SMALLINT [UNSIGNED], INTEGER [UNSIGNED], BIGINT [UNSIGNED], FLOAT4, FLOAT8, and NUMERIC.
    • Date and time types: DATE, TIME WITHOUT TIME ZONE, DATETIME, and TIMESTAMPTZ.
  2. For the floating-point type in the numeric type, the precision may be different from that in MySQL due to different connection parameter settings. Therefore, this scenario is not recommended, or the NUMERIC type is used instead.

16

SHA()/SHA1()

Supported.

-

17

SHA2()

Supported.

-
Parent topic: System Functions