Cong LiString 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!
