4

I'm trying to join Table1 and Table2 on the Url fields. However all the Urls in Table2 end with a "/" as per the example below:

Table1

╔═════════════════════╗
║ Url1                ║
╠═════════════════════╣
║ http://site1.com    ║
║ http://site2.com    ║
║ http://site3.com    ║
║ http://site4.com    ║
║ http://site5.com    ║
╚═════════════════════╝

Table2

╔═════════════════════╗
║ Url2                ║
╠═════════════════════╣
║ http://site1.com/   ║
║ http://site2.com/   ║
║ http://site3.com/   ║
║ http://site4.com/   ║
║ http://site5.com/   ║
╚═════════════════════╝

I'm using a SUBSTRING to remove the final character from the Url2 field. This is what my query looks like:

SELECT Table1.Url1,
       SUBSTRING(Table2.Url2, 1, LEN(Table2.Url2) - 1) AS Urlx
FROM Table2
LEFT JOIN Table1 ON Urlx = Table1.Url1

However I cannot get the Urlx field in the LEFT JOIN clause to resolve.

Is it possible to join tables on a manipulated field or have I constructed my query incorrectly?

3

2 Answers 2

3

Move the substring condition to the join clause and note that I added another record http://site6.com for testing purpose

SELECT tbl1.Url1,
       tbl2.Url2
FROM tbl1
LEFT JOIN tbl2 ON   SUBSTRING(tbl2.Url2, 1, LEN(tbl2.Url2) - 1) = tbl1.Url1

Result:

   Url1                  Url2
http://site1.com  http://site1.com/
http://site2.com  http://site2.com/
http://site3.com  http://site3.com/
http://site4.com  http://site4.com/
http://site5.com  http://site5.com/
http://site6.com

Demo

1
  • Thanks! This works exactly as I needed :)
    – neptr
    Feb 8 at 17:36
4

In this case, you'll need to put the function in the WHERE clause. This is not a good practice, as the function will need to run on every row in the table before checking for equality. A better approach would be to format the data properly on insert, so that both tables already have equal values.

However, if that's not an option, and you must run this query, I'd suggest this approach.

Try using the TRIM function in the WHERE clause instead of a SUBSTRING. The SUBSTRING will always remove the last character (or whichever position is specified). The problem with that will present itself the moment the '/' is not always a trailing character of the string. In that case, you'll trim a valid URL character.

Try this query.

SELECT tbl1.Url1,
    tbl2.Url2
FROM tbl1
LEFT JOIN tbl2 ON TRIM('/' FROM tbl2.Url2) = tbl1.Url1

Note: The TRIM function is only available on SQL Server 2017 and above.

Fiddle

TRIM documentation and syntax

4
  • 1
    Thanks for the fiddle. I will be studying that and may change my approach once I have a better understanding :)
    – neptr
    Feb 8 at 17:38
  • 1
    TRIM, by default removes, spaces from the beginning and end of a string. By including '/' in the function it removes '/' from the beginning or end of the string. docs.microsoft.com/en-us/sql/t-sql/functions/… Feb 8 at 17:42
  • 1
    Fantastic! Thanks again! I'm using the TRIM in my query :)
    – neptr
    Feb 8 at 17:48
  • 2
    It might be worth adding that TRIM is supported from 2017 and higher.
    – Zikato
    Feb 23 at 7:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.