Whole Database/Database and Table Sharding Task Conversion Function

Last updated:2025-06-23 15:03:46

Whole Database/Database and Table Sharding Task Conversion Function

Last updated: 2025-06-23 15:03:46

How to Use Conversion Function
Note：
Currently, conversion functions are only supported for Whole Database/Database and Table Sharding tasks with MySQL, TDSQL-C MySQL, and TDSQL MySQL source types.
For an Whole Database/Database and Table Sharding task, if necessary to handle fields of partial tables, you can edit a single table through map match with the preview feature.
The single table edit page supports adding function fields to convert existing data and synchronize it to the target table after processing. For the supported function scope, see the supported function list below.
The single table editing page supports data filtering. The filter statement also supports function transformation for existing fields. For the supported function scope, see the supported function list below.
List of Supported Function
Note:
The string in the function input parameter needs to be wrapped in two layers of single quotes, for example: ''string''.
Function Type
Function Name
Function Description
String Functions
REGEXP_REPLACE(string1, string2, string3)
Return the string in STRING1 where all substrings matching the regular expression STRING2 are consecutively replaced with STRING3. Example: REGEXP_REPLACE(id, '[a-zA-Z]', '') represents replacing all regex matches of [a-zA-Z] in the string id with an empty string.
﻿
CHAR_LENGTH(string)
Return the number of characters in STRING.
﻿
UPPER(string)
Return uppercase string.
﻿
LOWER(string)
Return lowercase string.
﻿
TRIM(string1)
Return the string with leading and trailing spaces removed.
﻿
SUBSTR(string, integer1[, integer2])
Return the substring of STRING starting at position integer1 with length integer2 (default to the end).
﻿
CONCAT(string1, string2,…)
Return the string concatenated from string1, string2, etc. For example, CONCAT('AA', 'BB', 'CC') returns 'AABBCC'. 
Time Function
DATE_FORMAT(timestamp, string)
Convert the timestamp to a date format string with the specified string value. The format string is compatible with Java's SimpleDateFormat.
﻿
TO_DATE(string1[, string2])
Convert the date format of string1 to the date format of string2 (default converts to 'yyyy-MM-dd' format).
﻿
TO_TIMESTAMP(string1[, string2])
Convert the date and time string string1 in string2 format (default: 'yyyy-MM-dd HH:mm:ss') to a timestamp without time zone.
﻿
FROM_UNIXTIME(numeric[, string])
Return the string representation of the value parameter (default: 'yyyy-MM-dd HH:mm:ss'). The value is an internal timestamp representing seconds since UTC time '1970-01-01 00:00:00', for example, a value generated by the UNIX_TIMESTAMP() function. The return value is represented in the session time zone (specified in TableConfig). For example, in the UTC time zone, FROM_UNIXTIME(44) returns '1970-01-01 00:00:44', but in the 'Asia/Tokyo' time zone, it returns '1970-01-01 09:00:44'.
﻿
UNIX_TIMESTAMP(string1[, string2])
Convert the date and time string string1 (format string2, default yyyy-MM-dd HH:mm:ss) to a Unix timestamp (in seconds) using the time zone specified in the table configuration. If the date and time string specifies a time zone in UTC+X format, such as 'yyyy-MM-dd HH:mm:ss.SSS X', this function will use the time zone specified in the date and time string rather than the one in the table configuration. If the date and time string cannot be parsed, it returns the default value Long.MIN_VALUE (-9223372036854775808).
Conditional Function
CASE value WHEN value1_1 [, value1_2]* THEN RESULT1 (WHEN value2_1 [, value2_2 ]* THEN result_2)* (ELSE result_z) END
When the value is first included in (valueX_1, valueX_2, …), return resultX. If no value matches and result_z is provided, return result_z; otherwise, return NULL.
﻿
CASE WHEN condition1 THEN result1 (WHEN condition2 THEN result2)* (ELSE result_z) END
When the first conditionX is met, return resultX. If no condition is satisfied and result_z is provided, return result_z; otherwise, return NULL.
﻿
COALESCE(value1 [, value2]*)
Return the first non-NULL parameter. If all parameters are NULL, return NULL. The return type is the least restrictive and most common type among all parameters. If all parameters are also nullable, the return type is nullable.
﻿
IF(condition, true_value, false_value)
If the condition is met, return true_value; otherwise, return false_value. For example, IF(5 > 3, 5, 3) returns 5.
Format conversion function
 CAST( <EXPR> AS <T> )
Convert any valid expression <EXPR> to a specific type <T>.
Hash Function
MD5(string)
Return the MD5 hash value of a string as a 32-digit hexadecimal number in string form; if the string is NULL, return NULL.
JSON Function
JSON_VALUE_GETTER(jsonValue, path)
Extract a scalar from a JSON string. This method searches the JSON string for the given path expression. If the value at this path is a scalar, it returns this value. It cannot return non-scalar values. By default, the return value is STRING.

Was this page helpful?

You can also Contact Sales or Submit a Ticket for help.

Yes

Feedback

Function Type	Function Name	Function Description
String Functions	REGEXP_REPLACE(string1, string2, string3)	Return the string in STRING1 where all substrings matching the regular expression STRING2 are consecutively replaced with STRING3. Example: REGEXP_REPLACE(id, '[a-zA-Z]', '') represents replacing all regex matches of [a-zA-Z] in the string id with an empty string.
		CHAR_LENGTH(string)	Return the number of characters in STRING.
		UPPER(string)	Return uppercase string.
		LOWER(string)	Return lowercase string.
		TRIM(string1)	Return the string with leading and trailing spaces removed.
		SUBSTR(string, integer1[, integer2])	Return the substring of STRING starting at position integer1 with length integer2 (default to the end).
		CONCAT(string1, string2,…)	Return the string concatenated from string1, string2, etc. For example, CONCAT('AA', 'BB', 'CC') returns 'AABBCC'.
Time Function	DATE_FORMAT(timestamp, string)	Convert the timestamp to a date format string with the specified string value. The format string is compatible with Java's SimpleDateFormat.
		TO_DATE(string1[, string2])	Convert the date format of string1 to the date format of string2 (default converts to 'yyyy-MM-dd' format).
		TO_TIMESTAMP(string1[, string2])	Convert the date and time string string1 in string2 format (default: 'yyyy-MM-dd HH:mm:ss') to a timestamp without time zone.
		FROM_UNIXTIME(numeric[, string])	Return the string representation of the value parameter (default: 'yyyy-MM-dd HH:mm:ss'). The value is an internal timestamp representing seconds since UTC time '1970-01-01 00:00:00', for example, a value generated by the UNIX_TIMESTAMP() function. The return value is represented in the session time zone (specified in TableConfig). For example, in the UTC time zone, FROM_UNIXTIME(44) returns '1970-01-01 00:00:44', but in the 'Asia/Tokyo' time zone, it returns '1970-01-01 09:00:44'.
		UNIX_TIMESTAMP(string1[, string2])	Convert the date and time string string1 (format string2, default yyyy-MM-dd HH:mm:ss) to a Unix timestamp (in seconds) using the time zone specified in the table configuration. If the date and time string specifies a time zone in UTC+X format, such as 'yyyy-MM-dd HH:mm:ss.SSS X', this function will use the time zone specified in the date and time string rather than the one in the table configuration. If the date and time string cannot be parsed, it returns the default value Long.MIN_VALUE (-9223372036854775808).
Conditional Function	CASE value WHEN value1_1 [, value1_2]* THEN RESULT1 (WHEN value2_1 [, value2_2 ]* THEN result_2)* (ELSE result_z) END	When the value is first included in (valueX_1, valueX_2, …), return resultX. If no value matches and result_z is provided, return result_z; otherwise, return NULL.
		CASE WHEN condition1 THEN result1 (WHEN condition2 THEN result2)* (ELSE result_z) END	When the first conditionX is met, return resultX. If no condition is satisfied and result_z is provided, return result_z; otherwise, return NULL.
		COALESCE(value1 [, value2]*)	Return the first non-NULL parameter. If all parameters are NULL, return NULL. The return type is the least restrictive and most common type among all parameters. If all parameters are also nullable, the return type is nullable.
		IF(condition, true_value, false_value)	If the condition is met, return true_value; otherwise, return false_value. For example, IF(5 > 3, 5, 3) returns 5.
Format conversion function	CAST( <EXPR> AS <T> )	Convert any valid expression <EXPR> to a specific type <T>.
Hash Function	MD5(string)	Return the MD5 hash value of a string as a 32-digit hexadecimal number in string form; if the string is NULL, return NULL.
JSON Function	JSON_VALUE_GETTER(jsonValue, path)	Extract a scalar from a JSON string. This method searches the JSON string for the given path expression. If the value at this path is a scalar, it returns this value. It cannot return non-scalar values. By default, the return value is STRING.

tencent cloud

How to Use Conversion Function

List of Supported Function

Function Type

Function Name

Function Description