I am trying to make an inner join on columns in which a value is stored differently, for example, in one column...
it is application:username and in another it is username (not starting with application). Can you explain how to do this with an example?
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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading