SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select *
into t
from (values 
             ('Males 40Y to 50Y'),
             ('Mixed Sex 35 to 40'),
             ('21Y only'),
             ('Year7 boys'),
             ('Year10 girls'),
             ('Grade1'),
             ('Random Text')
     ) v(text)
7 rows affected

select *
into lookup
from (values ('Year7 ', 13),
             ('Year10', 16),
             ('Grade1', 6)
     ) v(keyword, age)
3 rows affected

select t.text, coalesce(l.age, x.num)
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, '%')


text
(No column name)


Males 40Y to 50Y
50

Mixed Sex 35 to 40
40

21Y only
21

Year7 boys
13

Year10 girls
16

Grade1
6

Random Text
null


Warning: Null value is eliminated by an aggregate or other SET operation.

text	(No column name)
Males 40Y to 50Y	50
Mixed Sex 35 to 40	40
21Y only	21
Year7 boys	13
Year10 girls	16
Grade1	6
Random Text	null