"G-Moment": A Deep Dive into Frequently Used String Functions in GBase 8s

Published on 2025-08-08

String manipulation is a common requirement in database development. GBase 8s provides a variety of powerful string functions, enabling developers to process string data efficiently. This article details commonly used string processing functions, including REPLACE, SUBSTR, SUBSTRING, LPAD, and RPAD, and provides practical application examples.

REPLACE Function

(1) Syntax

REPLACE(string, find_string, replace_with)

  • string : The original string.

  • find_string : Specifies the characters to find and replace.

  • replace_with : Specifies the new replacement string.

(2) Example

Assume a table named stock with stock_num, unit, and unit_price columns. We want to replace 'each' with 'item' in the unit column:

SELECT stock_num, REPLACE(unit, 'each', 'item') AS cost_per, unit_price FROM stock WHERE manu_code = 'HRO';

Result:

SUBSTRING Function

(1) Syntax

SUBSTRING(string, pos, len)

  • string : The original string.

  • pos : The starting position for extraction.

  • len : The length of string to extract.

(2) Example

1. Extracting from the beginning of a string

SELECT sname, SUBSTRING(sname FROM 1 FOR 4) FROM state WHERE code = 'AZ';

Result:

2. Extracting from a specified position to the end

SELECT sname, SUBSTRING(sname FROM 6) FROM state WHERE code = 'WV';

Result:

3. Extracting from a negative position

SELECT sname, SUBSTRING(sname FROM -2 FOR 4) FROM state WHERE code = 'AZ';

Result:

SUBSTR Function

The SUBSTR function is similar to SUBSTRING but handles negative positions differently.

(1) Syntax

SUBSTR(string, pos, len)

  • string : The original string.

  • pos : The starting position for extraction.

  • len : The length of string to extract.

(2) Example

1. Extracting from a specified position to the end

SELECT sname, SUBSTR(sname, 2) FROM state WHERE code = 'AZ';

Result:

2. Extracting from a negative position

SELECT sname, SUBSTR(sname, -3, 2) FROM state WHERE code = 'AZ';

Result:

LPAD Function

(1) Syntax

LPAD(string, len, padstr)

  • string : The original string.

  • len : The length of the returning string.

  • padstr : The string to pad on the left side of the original string.

(2) Example

1. Padding a string

SELECT sname, LPAD(sname, 15, '-') FROM state WHERE code = 'AZ';

Result:

2. Truncating a string

SELECT sname, LPAD(sname, 2, '-') FROM state WHERE code = 'AZ';

Result:

RPAD Function

(1) Syntax

RPAD(string, len, padstr)

  • string : The original string.

  • len : The length of the returning string.

  • padstr : The string to pad on the right side of the original string.

(2) Example

1. Padding a string

SELECT sname, RPAD(sname, 15, '-') FROM state WHERE code = 'AZ';

Result:

2. Truncating a string

SELECT sname, RPAD(sname, 2, '-') FROM state WHERE code = 'AZ';

Result:

GBase 8s provides a diverse set of string functions, helping developers process string data efficiently. Through this article, we've covered the usage of the following functions:

  • REPLACE: Replaces specified characters within a string

  • SUBSTRING: Extracts a specified portion of a string

  • SUBSTR: Similar to SUBSTRING, but handles negative starting position differently

  • LPAD: Pads the left side of a string with specified characters

  • RPAD: Pads the right side of a string with specified characters

These functions are highly practical in real-world development, enabling you to handle string data more flexibly. If you have any questions or need further assistance, feel free to post in our community forum.