MD5_CONCAT_WS

Purpose

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

Limitations and considerations

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

• You must specify a value that does not conflict with the input columns as the replacement for NULL. This ensures that the replacement value does not conflict with the input column values. The following example shows an 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. It is a string.
• replace_null_value: The value used to replace NULL values. It is a string.
• str1, ... , strN: The input values to be concatenated (variable parameters). They are a list of strings.

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 with _ as the separator. Replace NULL values with @, and 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