Preprocessing functions|V3.0.0|OceanBase Loader and Dumper| docs|Distributed Database

You can configure a preprocessing function for each column when you define a control file.

Usage notes

For a preprocessing function configured for a column, only the values of the current column can be used as formal parameters. For example, c14 "concat(c15, '_suffix')" is not supported. Only c15 "concat(c15, '_suffix')" is supported.
LPADB() and RPADB() are suitable for processing characters such as letters and numbers. It may be truncated when you use it to process emojis.
REPLACE() is used differently in Oracle and MySQL modes. It has the syntax that is similar to an Oracle function but is implemented like a MySQL function.
NVL() refers to the corresponding built-in function in MySQL. It distinguishes an empty character from NULL in implementation.
For preprocessing functions related to date and time, such as SYSTIMESTAMP(), TMSFMT(), and TO_TIMESTAMP(), the time is accurate to milliseconds. In addition, the server clock must be the same as that of the database.
LPAD(char,length[,pad_string]) and RPAD(char,length[,pad_string]) refer to the corresponding built-in functions of MySQL. The length parameter indicates the total length of the return value that is displayed on the client. In most cases, it displays the actual number of characters in the return value. However, for some multibyte character sets, the displayed length of a string may differ from the actual length. Therefore, we recommend that you do not use this function to process multibyte values.

Functions

The following table describes the preprocessing functions supported in the current version.

Function signature	Return type	Description
LOWER(char)	String	Converts letters in a parameter value to lowercase letters. The parameter value can be a column name, constant, or function expression. If the parameter value is a constant, quote it with single quotation marks (' '). Example: `LOWER('A')`.
LTRIM(char[,set])	String	Matches and truncates a parameter value from left to right. The value of the `char` parameter can be a column name, constant, or function expression. The value of the `set` parameter is a constant. By default, if the `set` parameter is omitted, it is matched as a space. Example: `LTRIM(' abc ')`.
RTRIM(char[,set])	String	Matches and truncates a parameter value from right to left. The value of the `char` parameter can be a column name, constant, or function expression. The value of the `set` parameter is a constant. By default, if the `set` parameter is omitted, it is matched as a space. Example: `RTRIM(' abc ')`.
SUBSTR(char,position[,length ])	String	Truncates a parameter value based on the specified start position and length. The value of the `char` parameter can be a column name, constant, or function expression. The values of the `position` and `length` parameters are numeric constants. Example: `SUBSTR('abc',0,3)`.
TRIM(char)	String	Truncates leading and trailing spaces of a parameter value. The value of the `char` parameter can be a column name, constant, or function expression. Example: `TRIM(' abc ')`.
UPPER(char)	String	Converts letters in a parameter value to uppercase letters. The value of the `char` parameter can be a column name, constant, or function expression. If the parameter value is a constant, quote it with single quotation marks (' '). Example: `UPPER('a')`.
NANVL(char, default)	String	Verifies whether a parameter value is a number. If not, the default value is returned. The value of the `char` parameter can be a column name, constant, or function expression. The value of `default` is a constant. Example: `NANVL('1','1')`.
REPLACE(char,search[,replacement ])	String	Replaces a parameter value based on the specified search condition. The value of the `char` parameter can be a column name, constant, or function expression. The values of the `search` and `replacement` parameters are constants. Example: `REPLACE('abc','a','A')`.
NVL(char, default)	String	Verifies whether a parameter value is left empty. If yes, the default value is returned. The value of the `char` parameter can be a column name, constant, or function expression. The value of `default` is a constant. Example: `NVL('a','--')`.
TO_TIMESTAMP(char,fmt1[,fmt2])	String	Converts a parameter value into a date. If the conversion fails, `NULL` is returned. The value of the `char` parameter can be a column name, constant, or function expression. The value of the `fmt1` parameter is a date parsing template. The value of the `fmt2` parameter is a date formatting template, and the parameter is optional. By default, the date is returned in the yyyy-MM-dd HH:mm:ss.SSS format.
LENGTH(char)	String	Calculates the length of a parameter value. The value of the `char` parameter can be a column name, constant, or function expression.
LPAD(char,length[,pad_string])	String	Appends a character string of the specified length to the left of a parameter value. If the specified length is smaller than the length of the parameter value, the parameter value is truncated. The value of the `char` parameter can be a column name, constant, or function expression. The values of the `length` and `pad_string` parameters are constants. In addition, the value of the `pad_string` parameter must be a single-byte character. Example: `LPAD('a',1,'x')`.
RPAD(char,length[,pad_string])	String	Appends a character string of the specified length to the right of a parameter value. If the specified length is smaller than the length of the parameter value, the parameter value is truncated. The value of the `char` parameter can be a column name, constant, or function expression. The values of the `length` and `pad_string` parameters are constants. In addition, the value of the `pad_string` parameter must be a single-byte character. Example: `RPAD('a',1,'x')`.
CONVERT(char, charset1 [,charset2] )	String	Converts the encoding format of a parameter value. The value of the `char` parameter can be a column name, constant, or function expression. The values of the `charset1`and `charset2` parameters are constants. Example: `CONVERT('a','utf-8','gbk')`.
CONCAT(char1,char2)	String	Concatenates parameter values. The values of the `char1`and `char2` parameters can be a column name, constant, or function expression. Example: `CONCAT('a','b')`.
NONE()	String	Returns a parameter value without processing it.
SYSTIMESTAMP()	String	Returns the timestamp of the current server without processing the parameter value. The format is yyyy-MM-dd HH:mm:ss.SSS.
CONSTANT(char)	String	Returns a defined constant value without processing it. The value of the `char` parameter is a constant. Example: `CONSTANT('1')`.
TMSFMT(char,fmt1,default,fmt2)	String	Validates the date format of a parameter. If the validation fails, the default date format is returned. Example: `TMSFMT(c1,'yyyyMMddHHmmssSSS','2021-03-10 00:00:00.000','yyyy-MM-dd HH:mm:ss.SSS')`.
LPADB(char,byte_size[,pad_char])	String	Appends a byte string of the specified length to the left of a parameter value. If the specified length is smaller than the length of the parameter value, the parameter value is truncated. The value of the `char` parameter can be a column name, constant, or function expression. The values of the `byte_size` and `pad_char` parameters are constants. Example: `LPADB('a',1,'x')`.
RPADB(char,byte_size[,pad_char])	String	Appends a byte string of the specified length to the right of a parameter value. If the specified length is smaller than the length of the parameter value, the parameter value is truncated. The value of the `char` parameter can be a column name, constant, or function expression. The values of the `byte_size` and `pad_char` parameters are constants. Example: `RPADB('a',1,'x')`.

Notice

The groovy dynamic function is no longer supported in OBLOADER V3.x.

OceanBase

Customer Stories

Documentation

Community Edition

Enterprise Edition

Preprocessing functions

Usage notes

Functions