CODEWITHSUNDEEP
sqlsql-serversql-server-2008
mhn
mhn

Reputation: 2750

Join over substring across tables in MSSQL

I have two tables with one table with one column having a URL and another table having a substring from that URL

Table 1

Id  | URL
----------
1    ...\aaa_common\
2    ...\aaa_qa..
3    ...\aaa_test\Analytics

Table 2

SomeId | compname
-----------------
1         aaa_common
2         aaa_qa
3         aaa_test

It is possible to join using string functions (charindex and substring) . But is there an easier alternative?

Upvotes: 0

Views: 62

Answers (2)

AK47
AK47

Reputation: 3797

Yes you can use join, but not sure that this is best method, cause join on string is not good idea, also I am not sure about repetitive values in your table. Still if require to do so, I will suggest you to have one more column in your Table1, in which you can update only compname from same table using sub-string & then join both tables including new column from Table1 & compname from Table2.

Also for using sub-string you should be 100% sure with index/pattern of your compname in string of Table1.

Please look into this DEMO

Just has example of join on string using sub-string & charindex

Upvotes: 2

Ryan-Neal Mes
Ryan-Neal Mes

Reputation: 6263

You can join using like, but it will be a bit of a performance hit.

select
  *
from
  table_1 t1

  inner join table_2 t2 on
     t1.url like concat('%',t2.compname, '%')

Upvotes: 1

Related Questions