Search Text from one SQL table and match it from another SQL table and return values to a new column

Question

I'm not sure how to explain this so apologies for the title. I am hoping for some guidance on how to achieve the following:

I have an table with the following:

[dbo].[TABLEA]
 Name          Text
John Smith      . Tall. Smart. Blonde.
Jack Smith      . Medium. Low Intelligence. Black.
Jess Smith      . Short. Smart. Brunette.
Josh Smith      . Tall. Average Intelligence. Blonde. Smart.

I have a second table that looks like:

[dbo].[TABLEB]

Comment                         Major Category            Minor Category
. Tall.                         Height                     Tall
. Medium.                       Height                      Medium
. Short.                        Height                      Short
. Smart.                        Intelligence                Smart
. Average Intelligence.         Intelligence                Average
. Low Intelligence.             Intelligence                Low
. Blonde.                       Hair Colour                 Blonde
. Brunette.                     Hair Colour                 Brunette
. Black.                        Hair Colour                 Black

I am trying to search for the text within TABLEA and then add the Major Category colum with information from the Minor Category Column. Note if two minor categories appear in the same text, I am happy to just return the first one found.

The output I am trying to get is hopefully:

Name         Text                                           Height     Intelligence     Hair Colour
John Smith  . Tall. Smart. Blonde.                          Tall        Smart           Blonde
Jack Smith  . Medium. Low Intelligence. Black.              Medium      Low             Black
Jess Smith  . Short. Smart. Brunette.                       Short       Smart           Brunette
Josh Smith  . Tall. Average Intelligence. Blonde. Smart.    Tall        Average         Blonde

I am confident of doing it in Excel but SQL is a different story for me. Any guidance would be much appreciated! Thank you!

Answer 1

I think this does what you want:

select a.*, bh.minor_category as height, bi.minor_category as intelligence
from tablea a outer apply
     (select top (1) b.*
      from tableb b
      where b.major_category = 'height' and
            a.text like concat('%', b.text, '%')
     ) bh outer apply
     (select top (1) b.*
      from tableb b
      where b.major_category = 'intelligence' and
            a.text like concat('%', b.text, '%')
     ) bi;