Introduction to String Processing Functions in GBase 8s Database

String processing is a fundamental and essential task in database operations. GBase 8s offers various string processing functions, such as RPAD and LPAD, which enable developers to easily perform string padding and trimming within SQL queries. This article provides a detailed introduction to these string processing functions and includes usage examples.

RPAD Function

The RPAD function returns a copy of the source_string padded on the right to reach the total byte length specified by length.

RPAD(source_string, length [, pad_string])

Element	Description	Limit	Syntax
length	Total number of bytes in the return value	Must be an expression, constant, column, or host variable that returns an integer	Exact numerical value
pad_string	Characters specified as one or more padding characters	Must be an expression, constant, column, or a host variable with a data type convertible to character data types	Expression
source string	String used as the input for the PND function	Same as pad_string	Expression

All parameters of the RPAD function must be of built-in data types.

The pad_string parameter specifies one or more characters used to pad the source string.

The sequence of padding characters is repeated as many times as necessary to make the returned string reach the length specified by length. If the padding sequence in pad_string is too long to fit within length, it will be truncated. If you omit the pad_string parameter, the default value is a single space character (ASCII 32).

The return data type is based on the source_string and pad_string parameters, if both are specified. If the main variable is the source, the return value will be of type NVARCHAR or NCHAR, depending on the length of the returned string. This follows the return type promotion rules described in the return type section of the CONCAT function.

Even if the RPAD function appends blank characters to a data value, the UNLOAD feature of DB-Access truncates trailing spaces in CHAR or NCHAR columns. You must explicitly cast CHAR or NCHAR values to VARCHAR, LVARCHAR, or NVARCHAR data types if you need to preserve trailing blanks or non-printable characters returned by RPAD when unloading data.

In the following example, the user specifies that the source string should be right-padded to a total length of 18 characters. The user also specifies that the padding characters should be a sequence of question marks and exclamation points (?!).

SELECT RPAD('Where are you', 18, '?!') FROM mytable;

The table below shows the output of this SELECT statement:

(constant)
Where are you?!?!?

LPAD Function

The LPAD function returns a copy of the source_string padded on the left to reach the total byte length specified by length.

Element	Description	Limit	Syntax
length	Integer value specifying the total number of bytes in the returned string	Must be an expression, constant, column, or host variable convertible to an integer data type	Exact numerical value
pad string	Specifies one or more characters to use as padding	Must be an expression, constant, column, or host variable convertible to a character data type	Expression
source string	The input string for the LPAD function	Must be an expression, constant, column, or host variable convertible to a character data type	Expression

All parameters of the LPAD function must be of built-in data types.

The pad_string parameter specifies one or more characters to be used for padding the source string. The sequence of padding characters is repeated as many times as necessary to make the returned string reach the length specified by length.

If the padding sequence in pad_string is too long to fit within length, it will be truncated. If you do not specify a pad_string, the default is a single space character (ASCII 32).

The return data type is based on all three parameters, following the return type promotion rules described in the return type section of the CONCAT function.

In the following example, the user specifies that the source string should be left-padded to a total length of 16 bytes. The user also specifies that the padding characters should be a sequence of hyphens and underscores (-_).

SELECT LPAD('Here we are', 16, '-_') FROM mytable;

The table below shows the output of this SELECT statement:

(constant)
-_-_-Here we are

Use Case Example

WITH FUNCTION pr1 RETURN INT IS
    V1 CHAR(32767);
BEGIN
    V1 := RPAD('test', 500, 'p');
    RETURN (LENGTH(V1));
END;
SELECT pr1() FROM dual;
/

Through this article, you should now have a basic understanding of string processing functions in the GBase database. Mastering these functions can not only improve data processing efficiency but also help generate more standardized and aesthetically pleasing data outputs. In future articles, we will continue to explore other practical features of the GBase 8s database. Thank you for reading!