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

user_13194245 0
Viewed 48

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!!

ssms sql-server sql
1 Answers

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.

user_11440350
Related