How to Search SQL table column text and pick out a string or return the highest number

I have a column in a SQL table that looks like the following. It has age group information but it is quite random:

Table1
Text
Males 40Y to 50Y
Mixed Sex 35 to 40
21Y only
Year7 boys
Year10 girls
Grade1
Random Text

I have another table that categorises some of the age group data:

Lookup Table
Keywords      Age
Year7          13
Year10         16
Grade1         6

My end goal is to add a column to the original table the Age. I would like to lookup the Lookup Table first and then if there is no match, find the highest number within the string. If no match after that I would like to return the number 1 so that my end table looks like:

Text                    Age
Males 40Y to 50Y         50
Mixed Sex 35 to 40       40
21Y only                 21
Year7 boys               13
Year10 girls             16
Grade1                   6
Random Text              1

This is out of my abilties at the moment so looking for some help to solve this so any help would be appreciated. Thanks very much!!

Solution

Based on your sample data, you can use logic like this:

select t.text, coalesce(l.age, x.num, 1) as age
from t cross apply
     (select max(try_convert(int, replace(s.value, 'Y', ''))) as num
      from string_split(t.text, ' ') s
     ) x left join
     lookup l
     on t.text like concat('%', l.keyword, '%');

Note that this assumes that the largest value is either a number by itself or followed by "Y" -- which works for your sample data.

Here is a db<>fiddle.