MD5_CONCAT_WS

Purpose

This function concatenates multiple strings into one string, with the separator used to separate adjacent strings. If any of the parameters is NULL, it is replaced with replace_null_value before concatenation, and the MD5 hash of the concatenated string is returned as the result.

Limitations and considerations

• This function is available only in MySQL mode. In Oracle mode, it returns the error function unknown.

• At least 3 parameters (separator, replace_null_value, and at least one string to concatenate) are required. Otherwise, it returns the error Incorrect parameter count.

• LOB types such as TEXT, MEDIUMTEXT, LONGTEXT, BLOB, MEDIUMBLOB, and LONGBLOB are not supported as input parameters (except for TINYTEXT). Otherwise, it returns the error Not supported feature or function.

• This function may encounter hash collisions, leading to the same result for different inputs.

• You must specify a value that does not repeat in the input columns as the replacement for NULL. This ensures that the replacement does not result in duplicate values with the input columns. The following is an example of incorrect usage:

  MD5_CONCAT_WS('_', '@', 'a', NULL, 'b') = MD5('a_@_b')
  
  MD5_CONCAT_WS('_', '@', 'a', '@', 'b')  = MD5('a_@_b')
  

Syntax

MD5_CONCAT_WS(separator, replace_null_value, str1, ... , strN)

Parameters

• separator: the separator used to concatenate the input values.
• replace_null_value: the value used to replace NULL in the input.
• str1, ... , strN: the input values to be concatenated (variable number of arguments).

Return value

The MD5 hash of the concatenated string (32-bit hexadecimal).

Examples

1. Create a table named test_tbl1.

   obclient> CREATE TABLE test_tbl1 (
       col1 INT PRIMARY KEY,
       col2 VARCHAR(20) NOT NULL,
       col3 VARCHAR(50)
       );
   

2. Insert test data

   obclient> INSERT INTO test_tbl1 VALUES
       (1, 'A1', NULL),
       (2, 'A2', 'OceanBase'),
       (3, 'A3', 'DATABASE'),
       (4, 'A4', NULL),
       (5, 'A5', 'TABLEGROUP');
   

   The return result is as follows:

   Query OK, 5 rows affected
   Records: 5  Duplicates: 0  Warnings: 0
   

3. Concatenate the col1, col2, and col3 columns of the test_tbl1 table using _ as the separator, and replace NULL with @. Then, generate the MD5 hash of the concatenated string.

   obclient> SELECT MD5_CONCAT_WS('_', '@', t1.col1, t1.col2, t1.col3)
       FROM test_tbl1 t1;
   

   The return result is as follows:

   +----------------------------------------------------+
   | MD5_CONCAT_WS('_', '@', t1.col1, t1.col2, t1.col3) |
   +----------------------------------------------------+
   | 7df704a058d6e9293f01ae16c5c01fef                   |
   | a7518a709f593d475604511ab528351d                   |
   | fcd7a30f8897edfaf2f725aafc6af30c                   |
   | 1ad84ebb99c253544f2341f9c2b5dd57                   |
   | 26bb87a94aad329bae3d9cfb10b5614c                   |
   +----------------------------------------------------+
   5 rows in set