MD5_CONCAT_WS

Purpose

This function concatenates multiple strings into one string, using separator 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 resulting string is returned.

Considerations

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

• Ensure that the replacement value for NULL does not conflict with the input column values. Avoid using values that might repeat in the input column. 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: a string that serves as the separator when concatenating input values.
• replace_null_value: a string that replaces NULL values in the input.
• str1, ... , strN: a list of strings that are the input values to be concatenated (variable number of arguments).

Return value

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

Example

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 into a single string with underscores (_) and replace NULL values with @, and then generate the MD5 hash value.

   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