Red Devil Red Devil - 11 months ago 39
SQL Question

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

I have 2 table

Table A


Table B


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

Answer Source

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) )