CODEWITHSUNDEEP
sqlsql-server-2008
MGM
MGM

Reputation: 57

Replace in Joins

I have two tables Tbl_A and Tbl_B and data is as follows

Tbl_A

Company_nm  Website

Airindia    www.airindia.coin/index.html  
Spicejet    www.spicjet.com  
indigo      indigo.com/

Tbl_B

Company    URL

Airindia   www.airindia.co.in/index  
Spicejet   www.spicjet.com/index.html  
indigo     www.indigo.com/

My query is to select all the names of the companies by joining on website/Url columns of two tables by replaceing/removing additional characters after domain name. Final output should be all the 3 companies.

Upvotes: 0

Views: 79

Answers (4)

Patrick Hofman
Patrick Hofman

Reputation: 157048

You can use substring and charindex to get the first part of the url:

select *
from   tbl_a a
join   tbl_b b
on     case
       when charindex('/', a.website) > 0
       then substring(a.website, 0, charindex('/', a.website))
       else a.website
       end
       =
       case
       when charindex('/', b.url) > 0
       then substring(b.url, 0, charindex('/', b.url))
       else b.url
       end

Upvotes: 1

Toby Allen
Toby Allen

Reputation: 11211

Create a third column where you store the website or URL with only the domain name making matching faster & easier.

Upvotes: 0

user3844830
user3844830

Reputation: 44

As mentioned by @Parick answer i made changes and i think to use inner join to join the two table will be better idea in order to join matching data between two tables and join them together.

select *
from   tbl_a a
inner join   tbl_b b
on     substring(a.website, 0, charindex('/', a.website)) = substring(b.url, 0, charindex('/', b.url))

Upvotes: 0

Farzad Karimi
Farzad Karimi

Reputation: 780

i think you must use a 'pattern matching' way for your select query. have a look at Regular Expressions and Pattern Matching (Regex) for more Information.

Upvotes: 1

Related Questions