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?

one issue is your join clause has no idea what urlx is, the join gets processed before the select. try putting substring in join ON clause — Bob Klimes
– Bob Klimes, Commented Feb 8, 2022 at 15:38
Does this answer your question? Why are queries parsed in such a way that disallows the use of column aliases in most clauses? — mustaccio
– mustaccio, Commented Feb 8, 2022 at 17:09

Brendan McCaffrey · Accepted Answer · 2022-02-23 11:38:54Z

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

Thanks for the fiddle. I will be studying that and may change my approach once I have a better understanding :) — neptr
– neptr, Commented Feb 8, 2022 at 17:38
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/… — Brendan McCaffrey
– Brendan McCaffrey, Commented Feb 8, 2022 at 17:42
It might be worth adding that TRIM is supported from 2017 and higher. — Zikato
– Zikato, Commented Feb 23, 2022 at 7:43

Ergest Basha · Accepted Answer · 2022-02-08 16:57:16Z

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

Thanks! This works exactly as I needed :)

neptr
– neptr

2022-02-08 17:36:17 +00:00
Commented Feb 8, 2022 at 17:36 — neptr
– neptr, Commented Feb 8, 2022 at 17:36

Stack Exchange Network

SQL join on an edited field

2 Answers 2

Linked

Hot Network Questions

SQL join on an edited field

2 Answers 2

Linked

Related

Hot Network Questions