This is accomplished by using string functions to extract the username from the application:username values.
Consider these sample tables:
Table1 Table2
username username
Tom asdf:Tom
Dick asdf:Dick
Harry asdf:Harry
qwerty:Tom
qwerty:Dick
qwerty:Harry
asdfTom
Tom
oops:
The SUBSTRING function, which extracts a substring from a string value, will be needed. But what if there are application values of different lengths? Then we need to take the substring starting at a different point in the username column, and this will vary depending on where the colon is.
This is a job for the POSITION function.
SELECT Table1.username
, Table2.username
FROM Table1
INNER
JOIN Table2
ON SUBSTRING(Table2.username
FROM POSITION(':' IN Table2.username) + 1
) = Table1.username
Here, the POSITION function finds the position of the colon in Table2.username. For asdf:username it's in position 5, and for qwerty:username it's in position 7. By adding 1, we begin extracting the substring at the next character. Since there is no FOR length parameter specified, the substring goes all the way to the end. The extracted substring is then compared to Table1.username to match rows.
If the Table2.username value does not contain a colon, however, then the POSITION function returns 0 as the position. By adding 1, we begin extracting the substring at the first character. Thus the entire value will be compared to Table1.username to match rows. This may or may not result in a match, depending on the data, but at least the query will run. We need to add 1, simply because a FROM 0 value for the SUBSTRING function will usually fail.
Another problem is if the Table2.username value contains a colon but nothing after it. Then the POSITION value will be equal to the length of the string, and the FROM value will be 1 greater than that, so the SUBSTRING function might fail again. To get around this, just tack an extra space onto the Table2.username value:
ON SUBSTRING(Table2.username || ' '
FROM POSITION(':' IN Table2.username) + 1
) = Table1.username
If the colon is in the last position, then the SUBSTRING function will return the space. Of course, this probably won't match any Table1.username, so this is fairly safe. And luckily, trailing spaces do not make a difference when it comes to matching values, so the other rows will continue to join properly.
