CODEWITHSUNDEEP
mysqlsql
swashen
swashen

Reputation: 13

Creating a new column of extracted string sql

I have a table named summary with columns - Team Name and Resultin SQL.

Sample values:

Team Name | Result
team one  | team one won by 10 points
Champions | Champions lost by 15 points
Patriots  | Match tied

How do I get only the words 'won','lost' or 'tied' in a separate column? The team names maybe of multiple words.

Thanks in advance.

Upvotes: 1

Views: 47

Answers (2)

Tomasz
Tomasz

Reputation: 610

SQL code below (instr function used):

select s.*, case
  when instr(upper(s.result),'WON')>0 then 'W'  
  when instr(upper(s.result),'LOST')>0 then 'L'  
  when instr(upper(s.result),'TIED')>0 then 'T'  
  else '?' end as res
from summary s

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Here is one method:

select (case when s.result like '% won %' then 'won'
             when s.result like '% lost %' then 'lost'
             when s.result like '% tied%' then 'tied'
        end) as result
from t;

This might have some issues if the team name has the words "win", "lost", or "tied". This seems unlikely, but you can protect against it with:

select (case when s.result like team_name || ' ' || ' won %' then 'won'
             when s.result like team_name || ' ' || ' lost %' then 'lost'
             when s.result = team_name || ' tied' then 'tied'
        end) as result
from t;

Upvotes: 3

Related Questions