OceanBase
English日本語简体中文
Docs Home
Products
Integrations
Release Notes

Databases

OceanBase DatabaseOceanBase Cloud OceanBase Best Practices

Tools

OceanBase Cloud PlatformOceanBase Migration ServiceOceanBase Developer CenterOceanBase Migration AssessmentOceanBase Admin ToolOceanBase Loader and DumperOceanBase DeployerKubernetes operator for OceanBaseOceanBase Diagnostic Tool OceanBase Binlog Service

Connectors and Middleware

OceanBase Database Proxy OceanBase Connector/C OceanBase Connector/J OceanBase Connector/ODBC OceanBase Connector/NETOceanBase Command-Line Client

Databases

OceanBase DatabaseOceanBase Cloud OceanBase Best Practices

Connectors and Middleware

OceanBase Database Proxy OceanBase Connector/C OceanBase Connector/J OceanBase Connector/ODBC OceanBase Connector/NETOceanBase Command-Line Client

Tools

OceanBase Cloud PlatformOceanBase Migration ServiceOceanBase Developer CenterOceanBase Migration AssessmentOceanBase Admin ToolOceanBase Loader and DumperOceanBase DeployerKubernetes operator for OceanBaseOceanBase Diagnostic Tool OceanBase Binlog Service
OceanBase DatabaseOceanBase Cloud ToolsConnectors and MiddlewareLifecycle Support Policy

OceanBase Loader and Dumper

V4.3.7
  • Document Overview
  • Introduction
  • Technical Mechanism
  • Preparations
    • Prepare the environment
    • Prepare data
    • Download OBLOADER & OBDUMPER
    • Startup parameters
  • User Guide (OBLOADER)
    • Quick start
    • Command-line options
    • Multi-database import
    • Direct load
    • Data processing
      • Define a control file
      • Preprocessing functions
      • Case expressions
    • Use cases of command-line options
    • Performance tuning
    • Error handling
    • FAQ
  • User Guide (OBDUMPER)
    • Quick start
    • Command-line options
    • Multi-database export
    • Data processing
      • Define a control file
      • Preprocessing functions
      • Case expressions
    • Performance tuning
    • FAQ
  • Control file guide
  • Security Features
  • Connection configuration
  • Self-service Troubleshooting
  • Release Note
    • Release Note
      • 4.x
        • OBLOADER & OBDUMPER V4.3.7
        • OBLOADER & OBDUMPER V4.3.6
        • OBLOADER & OBDUMPER V4.3.5
        • OBLOADER & OBDUMPER V4.3.4.1
        • OBLOADER & OBDUMPER V4.3.4
        • OBLOADER & OBDUMPER V4.3.3.1
        • OBLOADER & OBDUMPER V4.3.3
        • OBLOADER & OBDUMPER V4.3.2.1
        • OBLOADER & OBDUMPER V4.3.2
        • OBLOADER & OBDUMPER V4.3.1.1
        • OBLOADER & OBDUMPER V4.3.1
        • OBLOADER & OBDUMPER V4.3.0
        • OBLOADER & OBDUMPER V4.2.8.2
        • OBLOADER & OBDUMPER V4.2.8.1
        • OBLOADER & OBDUMPER V4.2.8
        • OBLOADER & OBDUMPER V4.2.7
        • OBLOADER & OBDUMPER V4.2.6
        • OBLOADER & OBDUMPER V4.2.5
        • OBLOADER & OBDUMPER V4.2.4
        • OBLOADER & OBDUMPER V4.2.1
        • OBLOADER & OBDUMPER V4.2.0
        • OBLOADER & OBDUMPER V4.1.0
        • OBLOADER & OBDUMPER V4.0.0
      • 3.x
        • OBLOADER & OBDUMPER V3.3.2
        • OBLOADER \& OBDUMPER V3.1.0
        • OBLOADER \& OBDUMPER V3.0.0
    • Versioning rules

Download PDF

Document Feedback
  1. Documentation Center
  3. OceanBase Loader and Dumper
  5. V4.3.7

Preprocessing functions

Last Updated:2026-09-04 08:19:56

Considerations
Functions

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

Considerations

  • 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, and c15 "concat(c15, '_suffix')" is supported.

  • LPADB() and RPADB() are suitable for processing characters such as letters and digits. When you use either function to process an emoji, the emoji may be truncated.

  • REPLACE() in OBLOADER & OBDUMPER differs from that in Oracle and MySQL. It has the syntax that is similar to an Oracle function but is implemented like a MySQL function.

  • NVL() references 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 value is accurate only to milliseconds. In addition, the server clock must be the same as that of the database.

  • Preprocessing function names are case-insensitive.

  • LPAD(char,length[,pad_string]) and RPAD(char,length[,pad_string]) reference the corresponding built-in functions in MySQL. The length parameter indicates the total length of the return value that is displayed on the client. For most character sets, it is 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 into lowercase. The parameter value can be a column name, constant, or function expression. If the parameter value is a constant, enclose 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. If the set parameter is omitted, its value is replaced with a space by default.
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. If the set parameter is omitted, its value is replaced with a space by default.
Example: RTRIM(' abc ').
SUBSTR(char,position[,length ]) String Truncates a parameter value based on the specified start position and length. Positions start at 1: a positive value counts from the beginning of the string, 0 is treated as 1, and a negative value counts backward from the end of the string. 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.
Examples: SUBSTR('abcdef',2,3) returns bcd; SUBSTR('abcdef',-3,2) returns de; SUBSTR('abcdef',4) returns def; and SUBSTR('abc',0,3) returns abc.
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 ').
REVERSE(char) String Reverses parameter values.
For example, REVERSE("C1") reverses characters in each value of the C1 column.
UPPER(char) String Converts letters in a parameter value into uppercase. The value of char can be a column name, constant, or function expression. If the parameter value is a constant, enclose 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 the default parameter 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 the default parameter is a constant.
Example: NVL('a','--').
TO_TIMESTAMP(char,fmt1[,fmt2]) String Converts a parameter value into a date. The function returns NULL only when char is NULL. If a non-null value cannot be converted, the function throws an exception. 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 optional fmt2 parameter is the target date format.
Default value of fmt2: yyyy-MM-dd HH:mm:ss.SSS.
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 character set 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 column names, constants, or function expressions.
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.
Default format: yyyy-MM-dd HH:mm:ss.SSS.
SYSDATE String Returns the date on the current server without processing the parameter value.
Default format: yyyy-MM-dd HH:mm:ss.
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 and converts a date value. If the conversion fails, the function returns the replacement value specified by the third parameter, default. The fourth parameter, fmt2, is optional and specifies the target format. Its default value is yyyy-MM-dd HH:mm:ss.SSS.
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').
MASK(string str[, string upper[, string lower[, string number]]]) String Masks uppercase letters, lowercase letters, and digits in the values of the specified column.
For example, MASK("C1",A,a,b) masks the values of Column C1 by replacing uppercase letters with A, lowercase letters with a, and digits with b.
MASK_FIRST_N(string str[, int n]) String Masks the first n characters in the values of the specified column.
For example, MASK_FIRST_N("C1",'A','a','b',5) masks the first five characters in the values of Column C1 by replacing uppercase letters with A, lowercase letters with a, and digits with b.
MASK_LAST_N(string str[, int n]) String Masks the last n characters in the values of the specified column.
For example, MASK_LAST_N("C1",'A','a','b',5) masks the last five characters in the values of Column C1 by replacing uppercase letters with A, lowercase letters with a, and digits with b.
MASK_SHOW_FIRST_N(string str[, int n]) String Masks all characters except the first n characters in the values of the specified column.
For example, MASK_SHOW_FIRST_N("C1",'A','a','b',5) masks all characters except the first five characters in the values of Column C1 by replacing uppercase letters with A, lowercase letters with a, and digits with b.
MASK_SHOW_LAST_N(string str[, int n]) String Masks all characters except the last n characters in the values of the specified column.
For example, MASK_SHOW_LAST_N("C1",'A','a','b',5) masks all characters except the last five characters in the values of Column C1 by replacing uppercase letters with A, lowercase letters with a, and digits with b.
SEQUENCE(initial[,increment]) String Generates a sequence for a specified column. The initial parameter specifies the required start value. The optional increment parameter specifies the increment and defaults to 1.
For example, SEQUENCE(100) generates a sequence with a start value of 100 and an increment of 1.
CHR(string) String Converts a number represented by a string into an ASCII value.
  • If the value of string is greater than 256, the function is equivalent to 'string' mod 256.
    For example, the results of both CHR ('10') and CHR('266') are '\n'.
  • If the value of any parameter is NULL, the CHR() function returns NULL.
  • The parameter value can be a column name or constant. If the parameter value is a constant, enclose it with single quotation marks (' ').
    Examples: CHR('13') and CHR(c1).
NCHR(string) String Converts a number represented by a string into an NVARCHAR2 value.
  • Unlike the CHR() function, the NCHR() function can handle parameter values greater than 256.
    For example, NCHR('257') returns ā.
  • If the value of any parameter is NULL, the NCHR() function returns NULL.
  • The parameter value can be a column name or constant. If the parameter value is a constant, enclose it with single quotation marks (' ').
    Examples: NCHR('13') and NCHR(c1).
ASCII(char) String Converts a character into a decimal ASCII value.
  • If multiple characters are passed in, only the first character is converted.
    For example, both ASCII('A') and ASCII('abc') return '65'.
  • The parameter value can be a column name or constant. If the parameter value is a constant, enclose it with single quotation marks (' ').
    Examples: ASCII('A') and ASCII(c1).
RAWTOHEX(string) String Converts a raw string into a hexadecimal string.
The parameter value can be a column name or constant. If the parameter value is a constant, enclose it with single quotation marks (' ').
Examples: RAWTOHEX('ABC') and RAWTOHEX(c1).
HEXTORAW(string) String Restores a hexadecimal string to a raw string.
The parameter value can be a column name or constant. If the parameter value is a constant, enclose it with single quotation marks (' ').
Examples: HEXTORAW('414243') and HEXTORAW(c1).

Note

OBDUMPER can process only hexadecimal data that does not contain 0x or that is prefixed with 0x. If you want to use OBDUMPER to process hexadecimal data, you must use the nested replace function to remove the data that contains 0x but is not prefixed with 0x. Example: lang=java (c1 "hextoraw(replace(c1, '0x', ''))");.
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 value of the search parameter can be a constant or function expression. The value of the replacement parameter is a constant.
Examples: REPLACE('abc','a','A') and REPLACE('abc',CHR('13'),'!')
TRUNC(number[, decimal]) String Truncates a number.
  • number specifies the number to be truncated, and decimal specifies the number of decimal places to be retained after truncation. For example, TRUNC(22.29, 1) returns 22.2.
  • If decimal is not specified, the number specified by number is truncated to zero decimal places.
    For example, TRUNC(22.29) returns 22.
  • The value of decimal can be negative, which specifies to truncate the number specified by number to the left of the decimal point and set the digit on the left of the decimal point to zero.
    For example, TRUNC(22.29, -1) returns 20.

Note

The parameters of the function support any numeric data types and non-numeric data types that can be implicitly converted into numeric data types.
sm4_encrypt (plaintext, key [,encoding]) String Encrypts the given data by using the SM4 algorithm and returns the encrypted data.
The plaintext parameter specifies the original text, key specifies the Base64-encoded ciphertext, and encoding specifies the encoding type of the ciphertext. Supported encoding types are Base64 and HEX. The default encoding type is Base64.
Example: c1 "sm4_encrypt(c1, 'myKey')".
sm4_decrypt(cipherText, key [,encoding]) String Decrypts the encrypted data by using the SM4 algorithm and returns the raw data. The ciphertext parameter specifies the ciphertext, key specifies the Base64-encoded ciphertext, and encoding specifies the encoding type. Supported encoding types are Base64 and HEX. The default encoding type is Base64.
Example: c1 "sm4_decrypt(c1, 'myKey')".
TIMESTAMP_ADD (dateAndTime, addInt, datetimeFieldString, pattern: yyyy-MM-dd HH:mm:ss) String Increases or decreases values of the TIMESTAMP type.
  • datetimeFieldString specifies the unit. Valid values are YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, and MILLISECOND.
  • addInt specifies the value to be added or deducted. A minus sign specifies to deduct the specified value.
  • pattern is optional and specifies the original time format. The default value is yyyy-MM-dd HH:mm:ss.
    If the value of pattern is the same as its default value, this parameter is optional. If the value of pattern is different from its default value, this parameter is required.
Example with pattern specified: TIMESTAMP_ADD(c1, '1', 'MONTH', 'yyyy-MM-dd HH:mm:ss').
Examples with pattern unspecified: TIMESTAMP_ADD(c1, '1', 'MONTH'), which specifies to increase the values of Column c1 by one month.
TIMESTAMP_ADD(c1, '-2', 'WEEK') specifies to decrease the values of Column c1 by two weeks.
DATE_ADD (date, addInt, datetimeFieldString, pattern: yyyy-MM-dd) String Increases or decreases values of the DATE type.
  • datetimeFieldString specifies the unit. Valid values are YEAR, MONTH, WEEK, and DAY.
  • addInt specifies the value to be added or deducted. A minus sign specifies to deduct the specified value.
  • pattern is optional and specifies the original time format. The default value is yyyy-MM-dd.
    If the value of pattern is the same as its default value, this parameter is optional. If the value of pattern is different from its default value, this parameter is required.
Example with pattern specified: DATE_ADD(c1, '-1', 'YEAR', 'yyyy-MM-dd').
Example with pattern unspecified: DATE_ADD(c1, '-1', 'YEAR'), which specifies to decrease the values of Column c1 by one year.
DATE_TRUNC (date, datetimeFieldString, pattern: yyyy-MM-dd) String Truncates values of the DATE type to the specified field.
  • datetimeFieldString specifies the unit. Valid values are YEAR, MONTH, and DAY.
  • patternis optional and specifies the original date format. The default value is yyyy-MM-dd.
    If the value of pattern is the same as its default value, this parameter is optional. If the value of pattern is different from its default value, this parameter is required.
Example with pattern specified: DATE_TRUNC(c1, 'MONTH', 'yyyy-MM-dd').
Example with pattern unspecified: DATE_TRUNC(c1, 'MONTH'), which specifies to truncate the values of Column c1 to the month.
TIMESTAMP_TRUNC (dateAndTime, datetimeFieldString,pattern: yyyy-MM-dd HH:mm:ss) String Truncates values of the TIMESTAMP type to the specified field.
  • datetimeFieldString specifies the unit. Valid values are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
  • patternis optional and specifies the original date format. The default value is yyyy-MM-dd HH:mm:ss.
    If the value of pattern is the same as its default value, this parameter is optional. If the value of pattern is different from its default value, this parameter is required.
Example with pattern specified: TIMESTAMP_TRUNC(c1, 'HOUR', 'yyyy-MM-dd HH:mm:ss').
Example with pattern unspecified: TIMESTAMP_TRUNC(c1, 'HOUR'), which specifies to truncate the values of Column c1 to the hour.
DAY_OF_MONTH (dateAndTime,pattern: yyyy-MM-dd HH:mm:ss) String Returns the day (1 to 31) in the month for values of the TIMESTAMP type.
For example: DAY_OF_MONTH (c1) specifies to return the day in the month for values of Column c1.
DAY_OF_WEEK (dateAndTime,pattern: yyyy-MM-dd HH:mm:ss) String Returns the day of the week as an uppercase English string from MONDAY to SUNDAY.
For example, DAY_OF_WEEK (c1) returns the day of the week for values of Column c1.
DAY_OF_YEAR(dateAndTime,pattern: yyyy-MM-dd HH:mm:ss) String Returns the day (1 to 366) in the year for values of the TIMESTAMP type.
For example, DAY_OF_YEAR (c1) specifies to return the day in the year for values of Column c1.
HOUR(dateAndTime,pattern: yyyy-MM-dd HH:mm:ss) String Returns the hour (0 to 23) for values of the TIMESTAMP type.
For example, HOUR (c1) specifies to return the hour for values of Column c1.
MINUTE (dateAndTime,pattern: yyyy-MM-dd HH:mm:ss) String Returns the minute (0-59) for values of the TIMESTAMP type.
For example, MINUTE (c1) returns the minute for values of Column c1.
MONTH (dateAndTime,pattern: yyyy-MM-dd HH:mm:ss) String Returns the month as an uppercase English string from JANUARY to DECEMBER.
For example, MONTH (c1) returns the month for values of Column c1.
WEEK (dateAndTime,pattern: yyyy-MM-dd HH:mm:ss) String Returns the week for values of the TIMESTAMP type, namely, the week (1 to 53) in the year.
For example, WEEK (c1) specifies to return the week in the year for values of Column c1.
YEAR (dateAndTime,pattern: yyyy-MM-dd HH:mm:ss) String Returns the year for values of the TIMESTAMP type.
For example, YEAR (c1) returns the year for values of Column c1.
sm3_digest (plaintext [,encoding]) String Hashes data by using the SM3 algorithm and returns the extracted digest. The parameters are described as follows:
  • plaintext: the original text.
  • encoding: the encoding type for the generated digest. Supported encoding types are Base64 and HEX. The default value is HEX.

Examples: c1 "sm3_digest(c1)"
c2 "sm3_digest(c2, 'BASE64')".

Notice

Groovy dynamic functions are no longer supported in OBDUMPER V3.x and later.

Previous topic

Define a control file
Last

Next topic

Case expressions
Next
on this page
Considerations
Functions