CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Tester
Tester

Reputation: 43

MS SQL Join ´table by first 6 characters

Good day

I have two tables 

Table1 
Column1  Column2    
BoB      48J124BGAS11
Eva      V54318KFSGH5
John     465K33SH4674
Alex     7I7980HSH474

Table2 
Column1  Column2    
Apple    48J124
Samsung  V54318
Google   465K33
Android  348B00
Nokia    7I7980
Alcatel  158360

And I need Join them by firts 6 characters from right side of Column2. Expect result is like this.

Table3
Column1    Column2     Column3 
BoB      48J124BGAS11  Apple  
Eva      V54318KFSGH5  Samsung 
John     465K33SH4674  Google 
Alex     7I7980HSH474  7I7980

Iam not sure if i need create extra column in table1 for first 6 characters from right side of Column2 and then make join or i can create direct join.

Thanks for opinions.

Upvotes: 2

Views: 9262

Answers (3)

matrixw
matrixw

Reputation: 1

Use substring, on this way you can choose start position and signs count.

SELECT A.Column1, A.Column2, B.Coulmn1 
FROM Table1 AS A
JOIN Table2 AS B 
ON B.Coulmn2 = substring(A.Column2,0,6)

Upvotes: 0

dbajtr
dbajtr

Reputation: 2044

Is Table2 always going to have 6 characters in Column2?

Its possible to use the LEFT function to limit the join to the specific characters you want to use in the join, like this:

SELECT
*
FROM Table1 T1
INNER JOIN TABLE2 T2 ON LEFT(T1.Column2,6) = LEFT(T2.Column2, 6)

The inner join will get the results that exits in both tables, just alter the select * to get the columns that you want.

The reason I put the LEFT pad on both conditions in the join is because I didn't know the table structure restrictions

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37395

No, you don't need to create another column. Here's how you can join these tables:

SELECT A.Column1, A.Column2, B.Coulmn1 FROM Table1 AS A
JOIN Table2 AS B ON B.Coulmn2 = LEFT(A.Column2, 6)

Upvotes: 3

Related Questions