Functions（3）
Tags: opengauss
Published: 2023-03-09 04:51:47

**String Functions and Operators**
**·** string || string

Description: Concatenates strings.

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sbd020kf98myfs620yfu.png)

**·** bit_length(string)

Description: Specifies the number of bits occupied by a string.

Return type: int

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m0476kwd7bs4vmt86jm8.png)

**·** convert(string bytea, src_encoding name, dest_encoding name)

Description: Converts the bytea string to dest_encoding. src_encoding specifies the source code encoding. The string must be valid in this encoding.

Return type: bytea

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o1rkjav4frrh3yccq9y4.png)

**·** lower(string)

Description: Converts the string into the lowercase.

Return type: varchar

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/abgrj6xyynqkub30aleh.png)

**·** octet_length(string)

Description: Specifies the number of bytes in a string.

Return type: int

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fh636xqp54kfje078le5.png)

**·** overlay(string placing string FROM int [for int])

Description: Replaces substrings. **FROM int** indicates the start position of the replacement in the first string. **for int** indicates the number of characters replaced in the first string.

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xrf4wa4fj3jmrgquouyu.png)

**·** position(substring in string)

Description: Specifies the position of a substring. Parameters are case-sensitive.

Return type: int. If the character string does not exist, **0** is returned.

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u40cqo7mvru0zehph4w4.png)

**·** substring(string [from int] [for int])

Description: Extracts a substring. **from int** indicates the start position of the truncation. **for int** indicates the number of characters truncated.

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rvdaijglzml5ue6jd6hq.png)

**·** substring(string from pattern)

Description: Extracts substrings matching the POSIX regular expression. It returns the text that matches the pattern. If no match record is found, a null value is returned.

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s6jbcw3j8gn4gdmskaz8.png)

**·** trim([leading |trailing |both] [characters] from string)

Description: Removes the longest string containing only the characters (a space by default) from the start/end/both ends of the string.

Return type: varchar

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/58bh2qxrzepjr7i7881t.png)

**·** upper(string)

Description: Converts the string into the uppercase.

Return type: varchar

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5hwzbev827go3neerdg2.png)

**·** ascii(string)

Description: Indicates the ASCII code of the first character in the string.

Return type: integer

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ef5wwi2ve84ixozbq7bo.png)

**·** btrim(string text [, characters text])

Description: Removes the longest string consisting only of characters in characters (a space by default) from the start and end of string.

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mcdgvnc4ia7ui7jagy51.png)

**·** chr(integer)

Description: Specifies the character of the ASCII code.

Return type: varchar

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bch6mwt3t4x2yda0lmgo.png)

**·** convert(string bytea, src_encoding name, dest_encoding name)

Description: Converts the bytea string to dest_encoding. src_encoding specifies the source code encoding. The string must be valid in this encoding.

Return type: bytea

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i96nm7h8wy4lzcqmx37t.png)

**·** initcap(string)

Description: Converts the first letter of each word in the string into the uppercase and the other letters into the lowercase.

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0r1tyttdkp0qp4xbcd8a.png)

**·** length(string)

Description: Obtains the number of characters in a string.

Return type: integer

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q1b8t7xpxh0fzjgxt4oe.png)

**·** lpad(string text, length int [, fill text])

Description: Fills up **string** to **length** by appending the characters **fill** (a space by default). If **string** is already longer than **length**, then it is truncated.

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o5d10aljnul15pp4g8t3.png)

**·** ltrim(string [, characters])

Description: Removes the longest string containing only characters from characters (a space by default) from the start of string.

Return type: varchar

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rvy0l78p6kv4o9i7obsf.png)

**·** md5(string)

Description: Encrypts a string in MD5 mode and returns a value in hexadecimal form.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dffi9o3u858525gpmnnw.png)

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vzl59bhdxzjhpgu4twu0.png)

**·** repeat(string text, number int )

Description: Repeats **string** the specified number of times.

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sliy28somnrtg17ejwch.png)

**·** replace(string text, from text, to text)

Description: Replaces all occurrences in **string** of substring **from** with substring **to**.

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/55xbv9gns934s6r5919c.png)

**·** rpad(string text, length int [, fill text])

Description: Fills up **string** to **length** by appending the characters **fill** (a space by default). If **string** is already longer than **length**, then it is truncated.

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9d757coxauf5u00pwjxf.png)

**·** rtrim(string text [, characters text])

Description: Removes the longest string containing only characters from characters (a space by default) from the end of string.

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/si77cd9t74lsmwfynlrl.png)

**·** split_part(string text, delimiter text, field int)

Description: Splits **string** on **delimiter** and returns the _field_th column (counting from text of the first appeared delimiter).

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/phkr1soq7mp9ybce1pr3.png)

**·** strpos(string, substring)

Description: Specifies the position of a substring. It is the same as position(**substring in string**). However, the parameter sequences of them are reversed.

Return type: int

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p64q1x9j80w2knd2k2q2.png)

**·** to_hex(number int or bigint)

Description: Converts a number to a hexadecimal expression.

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8e2x6mhkmuzy93llesx6.png)

· translate(string text, from text, to text)

Description: Any character in **string** that matches a character in the **from** set is replaced by the corresponding character in the **to** set. If **from** is longer than **to**, extra characters occurred in from are removed.

Return type: text

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g6cdtijh0n18tobunj3c.png)