Functions and Expressions

Overview

The functions and expressions in MySQL do not exist in GaussDB(DWS) or are different from those in GaussDB(DWS). DSC migrates the functions and expressions based on the supported types of GaussDB(DWS). (compatible with ADB for MySQL)

Type Mapping

**Table 1** Type mapping
MySQL/ADB Function	Description	MySQL INPUT	GaussDB(DWS) OUTPUT
CONVERT	Converts a value to a specified data type or character set.	CONVERT(A, B)	CAST(A AS B)
CURDATE	Specifies the current date. It is synonymous with CURDATE().	CURDATE/CURDATE()	CURRENT_DATE ()
GET_JSON_OBJECT	Parses a field in a JSON string.	GET_JSON_OBJECT(column, '$.obj.arg') GET_JSON_OBJECT(column, '$[i]')	JSON_EXTRACT(column, 'obj', 'arg') JSON_ARRAY_ELEMENT(column, 0)
JSON_EXTRACT	Queries the value of a field in JSON.	JSON_EXTRACT(column, '$.obj')	JSON_EXTRACT(column, 'obj')
REGEXP	Fuzzy match	REGEXP A NOT REGEXP A	~ A !~ A
UUID	Generates a unique value (sequence number).	UUID	SYS_GUID
SPLIT()[]	Splits string on delimiter and returns the fieldth column (counting from text of the first appeared delimiter).	SPLIT(string text, delimiter text)[field int]	SPLIT_PART(string text, delimiter text,field int)
RAND	Obtains a random number ranging from 0.0 to 1.0.	RAND()	RANDOM()
SLICE	Character string splitting. The first argument is the separator for the rest of the arguments.	SLICE()	CONCAT_WS(sep text, str"any" [, str"any" [, ...] ])
TRY_CAST	Converts x into the type specified by y.	TRY_CAST(X AS Y)	CAST(X AS Y)
CAST	Converts x into the type specified by y. The second argument of the cast function in the ADB can be forcibly converted to the string or double-precision data type. In GaussDB(DWS), it is converted to the varchar or double precision type.	CAST(X AS Y)	CAST(X AS Y)

Input example: CONVERT

    
         SELECT CONVERT (IFNULL (BUSINESS_ID, 0) , DECIMAL(18, 2)) FROM ACCOUNT;

Output

    
         SELECT cast (ifnull (business_id, 0) AS decimal(18, 2))FROM account;

Input example: CURDATE

    
         SELECT CURDATE;
SELECT CURDATE();

Output

    
         SELECT CURRENT_DATE();
SELECT CURRENT_DATE();

Input example: GET_JSON_OBJECT

    
         SELECT GET_JSON_OBJECT(COL_JSON, '$.STORE.BICYCLE.PRICE');

SELECT GET_JSON_OBJECT(COL_JSON, '$.STORE.FRUIT[0]');

Output

    
         SELECT
  JSON_EXTRACT_PATH(COL_JSON, 'STORE', 'BICYCLE', 'PRICE');
SELECT
  JSON_ARRAY_ELEMENT(JSON_EXTRACT_PATH(COL_JSON, 'STORE', 'FRUIT'), 0);

Input example: JSON_EXTRACT

    
         SELECT JSON_EXTRACT(EVENT_ATTR,'$.TOPIC_ID');

Output

    
         SELECT JSON_EXTRACT_PATH(EVENT_ATTR, 'TOPIC_ID');

Input example: REGEXP

    
         SELECT * FROM USERS WHERE NAME NOT REGEXP '^ Wang';
SELECT * FROM USERS WHERE TEL REGEXP '[^4-5]{11}';

Output

    
         SELECT * FROM USERS WHERE NAME !~ '^ Wang';
SELECT * FROM USERS WHERE TEL ~ '[^4-5]{11}';

Input example: UUID

    
         SELECT CURDATE(str1), UUID(str2, str3) FROM T1;
SELECT A FROM B WHERE uuid() > 2;

Output

    
         SELECT current_date (str1),sys_guid (str2, str3) FROM T1;
SELECT A FROM  B WHERE sys_guid () > 2;

Input example: SPLIT()[]

    
         SELECT split('a-b-c-d-e', '-')[4];

Output

    
         SELECT split_part('a-b-c-d-e', '-', 4);

Input Example RAND

    
         SELECT rand();

Output

    
         SELECT random ();

Input Example: SLICE

    
         SELECT slice(split('2021_08_01','_'),1,3) from dual;

Output

    
         SELECT
  concat_ws(
    split_part('2021_08_01', '_', 1),
    split_part('2021_08_01', '_', 2),
    split_part('2021_08_01', '_', 3)
  )
FROM
  dual;

Input example: TRY_CAST

    
         select * from ods_pub where try_cast(pay_time AS timestamp) >= 1;
select try_cast(pay_time as timestamp) from obs_pub;

Output

    
         SELECT * FROM ods_pub WHERE cast (pay_time AS timestamp) >= 1;
SELECT cast (pay_time as timestamp) FROM obs_pub;

Input Example: CAST

    
         select cast(ifnull(c1, 0) as string) from t1;
select cast(ifnull(c1, 0) as varchar) from t1;
select cast(ifnull(c1, 0) as double) from t1;
select cast(ifnull(c1, 0) as int) from t1;

Output

    
         SELECT cast (ifnull (c1, 0) as varchar) FROM t1;
SELECT cast (ifnull (c1, 0) as varchar) FROM t1;
SELECT cast (ifnull (c1, 0) as double precision) FROM t1;
SELECT cast (ifnull (c1, 0) as int) FROM t1;