Check column A in table 1 if contains value from column A in table 2 and return the value of column B table 2

Question

In the this example, I am trying to see whether values in column A in table 1 appear within the values in column A table 2.

If it does I want to add column B in table 1, which returns the value of column B in table 2 where the matches occur.

Desired output:

Table 1

A	B
dhjasd sd Y02sd	105
Y02 dsd	105
sds Y0545 dsas dds	106
Y093 sad
sd Y304sd

Reference Table:

Table 2

A	B
Y01	102
Y06	103
Y02	105
Y08	110
Y0545	106

Can I please get any advice on how I should approach this? Thank you for your help!

Answer 1

Using dplyr you can do the following:

table1 |> 
  select(A) |> 
  mutate(values = stringr::str_extract(A, "Y\\d+")) |> 
  left_join(table2, by = c("values" = "A")) |> 
  select(A, B)

# A tibble: 5 × 2
  A                      B
  <chr>              <dbl>
1 dhjasd sd Y02sd      105
2 Y02 dsd              105
3 sds Y0545 dsas dds   106
4 Y093 sad              NA
5 sd Y304sd             NA