CODEWITHSUNDEEP
sqlsql-server-2008join
Red Devil
Red Devil

Reputation: 2393

Can we join table based on their length using len()

I have 2 table

Table A

nameA   
---------       
1234A   
1235A

Table B 

nameB   
----------  
1234        
1235

I want to join 1235A with 1235 and my expected outpout is 

nameA   nameB          
1234A   1234        
1235A   1235

Can we join both the table using len() of the name column

Upvotes: 0

Views: 284

Answers (3)

John D
John D

Reputation: 1637

You could try this - it matches names on either side up to the length of the smaller name. If the length parameter of substring is greater than the length of the input string it returns the original string.

select * from
TableA A inner join TableB B
on SUBSTRING( A.NameA, 1, len(B.NameB) ) = SUBSTRING( B.NameB, 1, len(A.NameA) )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270181

You could do something like this:

select a.name, b.name
from a join
     b
     on a.name like b.name + '%';

This question is very different from your original question. If you have another question, don't modify this one. Ask it as a new question.

Upvotes: 0

Abhay Chauhan
Abhay Chauhan

Reputation: 414

Select * from
TableA a join TableB b on
Left (a.nameA,4)= b.nameB

Try the above code.

Upvotes: 0

Related Questions