Modifying Fields to Accomplish Successful Joins

Carlton Patterson 741 Reputation points

I have tables and I would like to join on two fields. Unfortunately, in one table a field for website address appears as, whereas the website address in the other table appears as Therefore, naturally if I attempt to do a Inner Join on those fields there won't be a match.

The problem is the website address for both tables can be a mix of including https, http, or simply without http all together. However, the only common match will be the actual website address e.g. Therefore, I was wondering what would be the best approach when doing a join in these situations? Is there someway to just to a join on the website address

Sample data below
CREATE TABLE accweburl (
websiteurl varchar(100))

INSERT accweburl VALUES  
SELECT * FROM accweburl  
CREATE TABLE dueurl (  
    primaryWebsite varchar(50))  
SELECT * FROM dueurl  
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points

    One way

    Create table #Foo(Id int identity, WebAddress varchar(50));  
    Insert #Foo(WebAddress) Values  
    Select Id, Webaddress,  
        Case When CharIndex('://', WebAddress) = 0 Then 1 Else CharIndex('://', WebAddress)+ 3 End, Len(WebAddress)) As AdjustedWebAddress  
    From #Foo  
    Drop Table #Foo;  


1 additional answer

Sort by: Most helpful
  1. Carlton Patterson 741 Reputation points

    Having thought about this question a little more closely, I have come to realise the problem can't be solved with modifying the join criteria.

    The problem is a a data issue.

    Therefore, the question then becomes how to replace or remove http://, so that becomes

    0 comments No comments