Purpose
This function concatenates multiple strings into a single string, separating adjacent strings with separator. If any parameter contains NULL, replace_null_value is used to replace NULL during concatenation. The MD5 hash of the resulting concatenated string is then calculated and returned as the result.
Considerations
At least three parameters are required (
separator,replace_null_value, and at least one string to concatenate). Otherwise, anIncorrect parameter counterror is returned.LOB data types, such as
TEXT,MEDIUMTEXT,LONGTEXT,BLOB,MEDIUMBLOB, andLONGBLOB(except forTINYTEXT), cannot be used as input parameters. Otherwise, an errorNot supported feature or functionis returned.This function may encounter hash collisions, which means different inputs could result in the same output.
You must specify a value that does not duplicate any values in the input column as the replacement for
NULLvalues, to ensure that the result after replacingNULLvalues with this replacement value does not duplicate any values in the input column. The following usage is incorrect: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 is used as the delimiter when concatenating the input values.replace_null_value: A string to replace input values that areNULL.str1, ... , strN: A list of strings to be concatenated as input values (variable number of parameters).
Return value
Returns the MD5 value (32-bit hexadecimal) of the concatenated string.
Notice
If both separator and replace_null_value are specified and one of them is NULL, the function returns NULL.
Examples
Create the table
test_tbl1.obclient> CREATE TABLE test_tbl1 ( col1 INT PRIMARY KEY, col2 VARCHAR(20) NOT NULL, col3 VARCHAR(50) );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: 0Concatenate the columns
col1,col2, andcol3of tabletest_tbl1with an underscore_to form a string, replacingNULLvalues with@. Then generate an 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