To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
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.
|
