Exact Match with column separated with a delimiter and Appian Query
Is there a best practice with regards to finding an exact match from a delimited list inside a MySQL table column using a query rule?
Assume that standard, better solutions are out of scope - i.e. mapping tables to avoid the delimited lists inside columns, or using multiple rows for each ID and account number.
Example:
Clients Table
| ID | Account Numbers |
| 1 | 12345, 123456 |
| 2 | 123456, ABCDEF |
Query for 1234 should return no results (exact match desired). Query for 123456 should return 1,2. Query for 12345 should return 1, but not 2. ("includes" in Appian would return 1,2)
My current solution is using MD5, but wondering if there is a better method.
| ID | Account Numbers | Account Numbers MD5 |
| 1 | 12345, 123456 | 827CCB0EEA8A706C4C34A16891F84E7B, E10ADC3949BA59ABBE56E057F20F883E |
| 2 | 123456, ABCDEF | E10ADC3949BA59ABBE56E057F20F883E, 8827A41122A5028B9808C7BF84B9FCF6 |
Query using "includes" and MD5(parameter) correctly returns expected results.
i.e. A user inputs "12345" in a text field, it is passed into md5hash(), and that is passed to the query on the table which then uses "includes".
a!queryFilter(
field: "accountNumberMD5",
operator: "includes",
value: md5hash(ri!accountNumber)
)