SQL join on an edited field

Question

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?

Brendan McCaffrey · Accepted Answer · 2022-02-23T11:38:54.327

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

score 3 · Answer 2 · answered Feb 08 '22 at 16:57

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

SQL join on an edited field

2 Answers2