CODEWITHSUNDEEP
sqlsql-server-2008
InBetweeny
InBetweeny

Reputation: 5

join tables in sql where data does not exactly match up

I am using SQL Server 2008 and I have two tables that I want to join. I have provided something below that shows how my data looks. I want to join the two tables on the given columns, but how can I do this with the "ID" in front of the number in table B? I was thinking of a trim on the join, but I don't know how to do that.

Something like... 

Select *                    
From TableA AS A          
Left Join TableB AS B        
On A.ColumnA = B.ColumnB

But this won't work because the numbers don't completely match up.

TableA ColumnA   
123        
456        
789        

TableB ColumnB              
ID123                  
ID456                    
ID789

I hope I made this clear enough. Any suggestions?

Upvotes: 0

Views: 202

Answers (1)

SQLChao
SQLChao

Reputation: 7847

SQL Fiddle Demo 

select *
from tableA a
join tableB b 
  on 'ID' + cast(columnA as varchar(5)) = b.columnB

Upvotes: 2

Related Questions