8

I have a table A with the string-column a and a table B with the string-column b. a is a substring of b. Now I want to join the the two tables on a and b. Is this possible?

I want something like this:

Select * from A,B where A.a *"is substring of"* B.b

How can I write this in SQL (Transact-SQL)?

MackM
  • 2,906
  • 5
  • 31
  • 45
Elmex
  • 3,331
  • 7
  • 40
  • 64

6 Answers6

27

You can use like

select *
from A
  inner join B 
    on B.b like '%'+A.a+'%'
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 2
    Please don't do this if a lot of the values you're looking at are potentially blank. Speaking from experience, odds are you might end up flooding the whole database network with traffic. – Freerey Sep 03 '20 at 14:34
3
declare @tmp1 table (id int, a varchar(max))
declare @tmp2 table (id int, b varchar(max))


insert into @tmp1 (id, a) values (1,'one')
insert into @tmp2 (id,b) values (1,'onetwo')

select * from @tmp1 one inner join @tmp2 two on charindex(one.a,two.b) > 0

You can also use charindex, 0 means its not found, greater than 0 is the start index

charindex

Chris
  • 4,425
  • 5
  • 34
  • 49
1

set an inner join on a substring(4 letters) of FIELD1 of table TABLE1 with FIELD1 of table TABLE2

select TABLE1.field1,TABLE2.field1 from TABLE1 inner join TABLE2 on substring(TABLE1.field1,2,5)=TABLE2.field1
Matt
  • 45,022
  • 8
  • 78
  • 119
masoud Cheragee
  • 350
  • 3
  • 9
0
Select * from A
Join B on B.b = substr(A.a, 1, 5)

The number 1 represents the first character of your string but you would pick the begin and the end of the characters you would like to match of course. Could be 5, 9 too for example.

Belen
  • 1
  • 1
0

try this:

Select * from A,B where B.b LIKE '%'+A.a+'%'
Euclides Mulémbwè
  • 1,677
  • 11
  • 18
0

You have the contains function: http://msdn.microsoft.com/en-us/library/ms187787.aspx

select * from A,B where contains(B.b, A.a)
barsju
  • 4,408
  • 1
  • 19
  • 24