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

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
    Commented Feb 8, 2022 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. learn.microsoft.com/en-us/sql/t-sql/functions/… Commented Feb 8, 2022 at 17:42
  • 1
    Fantastic! Thanks again! I'm using the TRIM in my query :)
    – neptr
    Commented Feb 8, 2022 at 17:48
  • 2
    It might be worth adding that TRIM is supported from 2017 and higher.
    – Zikato
    Commented Feb 23, 2022 at 7:43
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
    Commented Feb 8, 2022 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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