Regex - Match substring with Strings

Your problem is that it checks from the top of DF1 when it looks for a match and returns the first match. If you sort the dataframe by the length of the strings in the column it returns the longest match instead.

This should fix your problem:

# Sort by length of string
new_index = df1.ID.str.len().sort_values().index
df1 = df1.reindex(index = new_index[::-1]).reset_index(drop=True)

# Match strings
df2['ID'].str.extract(fr"({'|'.join(df1['ID'].values)})", expand=False)

This behaviour is expected because "For each subject string in the Series, extract groups from the first match of regular expression pat." (https://pandas.pydata.org/docs/reference/api/pandas.Series.str.extract.html). So you're getting '123' instead of '123B' because '123' is the first value in the series in df1, and hence the first match.

All you need to do is to sort your df1['ID'] values by length in descending order using sorted(df1['ID'].values, key=len, reverse=True):

>>> df2['ID'].str.extract(fr"({'|'.join(sorted(df1['ID'].values, key=len, reverse=True))})", expand=False)
0     123
1    123B
2     NaN
3     234
4    234B
5     456
6     NaN
7     NaN
8     NaN
Name: ID, dtype: object 

You need this because longer alternative in NFA regex engines (Python's re is) always "wins" and all the other alternatives in the same group are not even tried by the regex engine. See Remember That The Regex Engine Is Eager. E,g. you hd 123|123B in your regex, so 123 matched 123 in 123B456 and returned that match, 123B alternative was not tried, and so on.