How to use string functions to make an SQL join

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.