CODEWITHSUNDEEP
sqlsql-server
nathan
nathan

Reputation: 83

SQL how to join on another field if the first field has letters in it

I have a SQL join that looks like this

INNER JOIN UPLOAD PH on cast(p.PickTicket_Number as varchar(20))=PH.FIELD004

However, sometimes, the field I really want to join on is PH.FIELD003. This happens when PH.FIELD004 is a different field that has letters in it. How do I include a condition in the ON clause where if field004 does not have letters in it (is numeric), it joins just like that above, but if it does have letters in it, it instead joins on the pickticket number = field003?

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271061

You can express the logic like this:

UPLOAD PH 
ON cast(p.PickTicket_Number as varchar(20)) = PH.FIELD004 OR
   (TRY_CONVERT(int, PH.FIELD004) IS NULL AND
    CAST(p.PickTicket_Number as varchar(20)) = PH.FIELD003
   )

Notes:

  • This checks that FIELD004 cannot be converted to an INT. This is hopefully close enough to "has letters in it". If not, you can use a CASE expression with LIKE.
  • OR is a performance killer for JOIN conditions. Function calls (say for conversion) also impede the optimizer.
  • If performance is an issue, ask a new question.
  • I am guessing the "number" value is an INT; if not, change to the appropriate type.
  • Converting numbers to strings for comparison can be dangerous. I've had problems with leading 0's, for example.

Because of the last issue, I would recommend:

UPLOAD PH 
ON TRY_CONVERT(INT, PH.FIELD004) = p.PickTicket_Number  OR
   (TRY_CONVERT(int, PH.FIELD004) IS NULL AND
    p.PickTicket_Number= PH.FIELD003
   )

Upvotes: 2

Related Questions