I need to form a join in SQL based on substrings in two columns, but the order of the substrings is not the same. In this example I'll use 'first name' and 'last name' as the required substrings. Is there something that will withstand this?
| ID1 | ID2 |
|---|---|
| Parker, Mary | Mary Parker |
| Davis-Jones, Jim | JIM Jones |
I have been able to figure out less demanding cases using JOIN ON with LOCATE/TRIM/SUBSTRING but the code is not good enough for the above.