MD5_CONCAT_WS

Description

This function concatenates multiple strings into one string with the separator between adjacent strings. If NULL is present in the parameters, it is replaced with replace_null_value before concatenation. The MD5 hash of the concatenated string is returned as the result.

Considerations

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

• Ensure that the replace_null_value does not conflict with the input column values to avoid duplicates. Incorrect usage is shown below:

  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 delimiter used to concatenate the input values.
• replace_null_value: The value used to replace NULL in the input.
• str1, ... , strN: The list of input values to be concatenated (variable number of arguments).

Return Value

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

tip

Both separator and replace_null_value must be specified. If one of them is NULL, the function returns NULL.

Examples

1. Create a table named test_tbl1.

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

2. Insert test data.

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

   The returned result is as follows:

   Query OK, 5 rows affected (0.002 sec)
   Records: 5  Duplicates: 0  Warnings: 0

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

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

   The returned result is as follows:

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