Different JOIN values depending on the value of another column

I have 2 tables j and c.

Both tables have columns port and sec.

For j.port = ABC, I want to join the 1st 6 characters of c.sec with the 1st 6 characters of j.sec.

For other j.ports, I want to join c.sec = j.sec

How can I do that ?

select c.port,j.port,c.sec,j.sec from j, c
where  c.SEC = 
   CASE WHEN j.port = 'ABC' then SUBSTRING(c.sec,1,6) = SUBSTRING(j.sec,1,6)  
   --> something like this
   else j.sec

Solution

Performance wise breaking this into two may be beneficial. The complex join condition will force nested loops otherwise.

SELECT c.port,
       j.port,
       c.sec,
       j.sec
FROM   j
       JOIN c
         ON LEFT(c.sec, 6) = LEFT(j.sec, 6)
WHERE  j.port = 'ABC'
UNION ALL
SELECT c.port,
       j.port,
       c.sec,
       j.sec
FROM   j
       JOIN c
         ON c.sec = j.sec
WHERE  j.port IS NULL
        OR j.port <> 'ABC'

Or in this specific case you could also do

  SELECT c.port,
       j.port,
       c.sec,
       j.sec
FROM   j
       JOIN c
         ON LEFT(c.sec, 6) = LEFT(j.sec, 6)
         and (j.port = 'ABC' OR c.sec = j.sec)

This allows the main join to be a simple equi join that can use any of the join algorithms with a residual predicate on the result.

For the following example data both of these took about 700ms on my machine whereas I killed the three competing answers after 30 seconds each as none of them completed in that time.

create table c(port varchar(10), sec varchar(10)  index ix clustered )  
create table j(port varchar(10), sec varchar(10))  

INSERT INTO c 
SELECT TOP 1000000 LEFT(NEWID(),10) , LEFT(NEWID(),10)
FROM sys.all_objects o1, sys.all_objects o2

INSERT INTO j 
SELECT TOP 1000000 LEFT(NEWID(),10) , LEFT(NEWID(),10)
FROM sys.all_objects o1, sys.all_objects o2