Sort and Join with letters and numbers

Question

I have two files (file1 and file2) and I would like to mount a third (file3) containing a column that the two do not have. I usually do the sort and then together with the join. But it is not working, because it has values with letters and numbers from the example below:

File1:

ARS-BFGL-BAC-1180 14 20574088
ARS-BFGL-BAC-11805 12 14897445
ARS-BFGL-BAC-11852 1 34309921
ARS-BFGL-BAC-11867 13 12616676 

File2

AC16359 ARS-BFGL-BAC-11798 B B
AC16359 ARS-BFGL-BAC-11805 B B
AC16359 ARS-BFGL-BAC-1180 B B
AC16359 ARS-BFGL-BAC-11852 A A
AC16359 ARS-BFGL-BAC-11867 B B 

I notice that I use the same sort command for both and note that the final SNP_Name 11805 is above 1180 and the other is not.

Answer 1

Sort by the matching fields only (i.e. -k1,1 for file1 and -k2,2 for file2).

e.g.

$ join -1 1 -2 2  <(sort -k1,1 file1) <(sort -k2,2 file2) 
ARS-BFGL-BAC-1180 14 20574088 AC16359 B B
ARS-BFGL-BAC-11805 12 14897445 AC16359 B B
ARS-BFGL-BAC-11852 1 34309921 AC16359 A A
ARS-BFGL-BAC-11867 13 12616676 AC16359 B B

I initially tried this using just a version sort (-V, --version-sort, which is a non-standard option which requires either GNU or *BSD sort). That worked too, both with and without the -k1,1 and -k2,2, but it may not work reliably with a larger or different set of data files.

Given that the key fields can contain either 4 or 5 digit numbers (and possibly more variations), using a natural sort is ideal. So, I'd use a version sort and specify the key fields.

join -1 1 -2 2  <(sort -V -k1,1 file1) <(sort -V -k2,2 file2)