Finding first matched string in a column in SQL Server

Question

I have a table as follows:

TABLE1

No  Description
1   Hello Hai Hello
2   Good Bad Good
3   Hello Good Hai
4   Hai Hello Bad
5   Hello Hello Hello

Now if I run the following query:

Select * 
from TABLE1 
where Description like '%Hai%' 
   OR  Description like '%Hello%' 
   OR Description like '%Good%'

I want the result as follows ( By adding one extra column "FIRST MATCHED WORD"):

No  Description     First Matched Word
1   Hello Hai Hello     Hai
2   Good Bad Good       Good
3   Hello Good Hai      Hai
4   Hai Hello Bad       Hai
5   Hello Hello Hello   Hello

Hear my intention is to find out the first matched word depends on our query. Kindly help me.

Answer 1

You can use CASE statement in SELECT query in following way:

Select No,Description, 
  CASE 
      WHEN Description like '%Hai%' THEN 'Hai' 
      WHEN Description like '%Hello%' THEN 'Hello'
      WHEN Description like '%Good%' THEN 'Good'
   END 
from TABLE1 
where Description like '%Hai%' 
   OR  Description like '%Hello%' 
   OR Description like '%Good%'

See SQLFIDDLE

Case Statement returns result of the first boolean expression that evaluates to TRUE.

Answer 2

try this:

If you want to add more words to match, you just have to add the word to the precedence table with a rank. If you have large number of words to match , you can make the precedence table as a permanent table

with precedence as(
select 1 rnk, 'Hai' as word union all
select 2 rnk, 'Hello' union all
select 3 rnk, 'Good' ),
cte as 
    (select * 
     from Table1
     join precedence
     on  [Description] like '%'+word+'%' ),
cte1 as(select [No],[Description],word,
               ROW_NUMBER() over (partition by [No] order by rnk) as row_num 
        from cte)
select [No],[Description],word from cte1 where row_num=1

SQL Fiddle demo