Manage Learn to apply best practices and optimize your operations.

How to use string functions to make an SQL join

SQL expert Rudy Limeback explains how to use string functions to make an SQL join using only a portion of a column value.

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

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
  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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.