Shell Programming and Scripting

Experts,

Im struggling with something for the past hour or so and here is the challenge.

File1:
DO93948388 LDCNND 343 48848 -- Row1
DKKDF9933433 DKK 3384774 DLLFLFD -- Row2
DKKFJ38383734 DJF934988 REPFD FD -- Row3


File2:
LDCNNDDKJF --- Row1
DNCNDNCND --- Row2
UFUUOP383 --- Row3
DLLFLFDKDKF63 --- Row4
DJF9349883477 --- Row5


File3(Output):
DO93948388 LDCNND LDCNNDDKJF
DKKDF9933433 DLLFLFD DLLFLFDKDKF63
DKKFJ38383734 DJF934988 DJF9349883477

In the above scenario, in File1, either Col2 or Col3 or Col4 might be a substring in File2's Column1 and if it is, then please fetch the matching Row from File 1 & File2 as shown above in File3.

In sql, we would probably code the above as something like this ...
instr( file2.col1, file1.col2) > 0 or
instr(file2.col1, file1.col3 ) > 0 or
instr(file2.col1, file1.col4) > 0.

Im not sure as how to go about this in unix.

Please help.
Lee.

Im not sure as how to go about this as

Code:

cat file1
DO93948388 LDCNND 343 48848
DKKDF9933433 DKK 3384774 DLLFLFD
DKKFJ38383734 DJF934988 REPFD FD

cat file2
LDCNNDDKJF
DNCNDNCND
UFUUOP383
DLLFLFDKDKF63
DJF9349883477

 
awk 'NR==FNR{a[NR]=$1;p=NR;next}{for(i=2;i<=NF;i++) {for(j=1;j<=p;j++) {if (sub($i,$i,a[j])) {print $1 FS $i FS a[j]}}}}' file2 file1
DO93948388 LDCNND LDCNNDDKJF
DKKDF9933433 DLLFLFD DLLFLFDKDKF63
DKKFJ38383734 DJF934988 DJF9349883477
DKKFJ38383734 FD DLLFLFDKDKF63

How about this:

Code:

awk 'NR==FNR{S[$0];next} {for(s in S) for(i=2;i<=NF;i++) if(s ~ "^"$i) print $1" "$i" "s; }' file2 file1

Thanks Chuber_XL, your code is much better than mine, I learn a lot from you

Yinyuemi & Chubler_XL,

Absolutely brilliant !!! I envy you folks.

Thanks a million.

Another request, lets say if I want to see just what is only in File2 or File1, how do I get that ?

Lee.

I this what your after?

Code:

awk 'NR==FNR{S[$0];next}
{for(s in S) for(i=2;i<=NF;i++) if($i&&s ~ "^"$i) {$i=x;S[s]="X"}
for(i=2;i<=NF;i++) if($i)print $i} END {for(s in S) if(S[s]!="X") print s}' file2 file1
343
48848
DKK
3384774
REPFD
FD
DNCNDNCND
UFUUOP383