CODEWITHSUNDEEP
sqlsql-serverjoin
ToddR
ToddR

Reputation: 11

SQL - Left Join - On part of string only

I'm trying to make a Left Join, should be simple enough, I have 2 problems;

Join, Left 3 characters of this one

convert(VARCHAR(max),(file_key7), 102)

in db [RF_Sydney].[dbo].[std_file]

with the Right 3 characters of this one

convert(VARCHAR(max),(code_key), 11)

in db [RF_Sydney].[dbo].[std_code]

Upvotes: 1

Views: 3413

Answers (3)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

In SQL Server, you can join on any condition that can be satisfied; in other words, you can do this:

SELECT *
FROM dbo.std_file f LEFT JOIN std_code c 
ON LEFT(convert(VARCHAR(max),(f.file_key7), 102), 3)
= RIGHT(convert(VARCHAR(max),(c.code_key), 11),3)

Performance will suck (unless you use persisted computed columns and define an index).

Upvotes: 3

terary
terary

Reputation: 1098

Do you know the length of file_key7 or code_key? Joining LEFT(str,len) = RIGHT(str,len) should work but possible take a big performance hit. Maybe you should create field/column and stick your partial keys in it already converted in the right character format

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415860

The best way to do this is to use a computed column in each of those tables. This will allow you to simplify your join code, and even allow you to define an index on the column to improve performance if you need it. As for getting the left and right value, there are LEFT() and RIGHT() functions you can use:

LEFT(convert(VARCHAR(max),(file_key7), 102), 3)

and 

RIGHT(convert(VARCHAR(max),(code_key), 11), 3)

For the join expression and query itself, we don't have enough information yet to know exactly how you want these to fit together.

Upvotes: 1

Related Questions