UNIX for Advanced & Expert Users

hi, i have
file 1:
======
0501000|X1
0502000|X2
0501231|X3
0981222|X4
0502000|X6
0503000|X7
0932322|X8

file 2:
=======
050
0501
0502
09
098


the required output is .. to find for each record, in (ORDER by LENGTH) to join
file 1 to file 2
: as example:

0501000|X1 ===> is joined to ===> 0501* & not 050
0502000|X2 ===> is joined to ===> 0502* & not 050
but
0503000|X7 ===> is joined to ===> 050*

this can be true to any length of file2: for example: it could be 050XX000 ==> 050XX0 & not (050X*, 050XX*)


can anybody help .. giving ideas using sort /join

note: file1: is 10.5 Million line or record
file2 : is 1000 record

Advice really appreaciated with performance kept in mind.
Thanks

If performance is a consideration then perl is probably not a good approach. If your data is in a database table, which you are already alluding to, then you need a smart use of the LIKE operator as well as wildcards ('%', '_' etc). Details of what is possible within MySQL can be found here: MySQL :: MySQL 5.0 Reference Manual :: 11.4.1 String Comparison Functions
Let me know what you ultimately will use, because I find the problem quite interesting.