在SQL Server中查找列中的第一个匹配字符串

Question

我有一个表，如下：

TABLE1

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

现在，如果我运行以下查询：

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

我希望结果如下所示(通过添加额外的列"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

我的意图是找出第一个匹配的单词取决于我们的查询。请帮帮我。

Answer 1

您可以通过以下方式在SELECT查询中使用CASE statement：

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%'

请参阅

Case Statement返回计算结果为TRUE的第一个布尔表达式的结果。

Answer 2

试试这个：

如果你想添加更多的词来匹配，你只需要将这个词添加到具有排名的优先级表中。如果您有大量要匹配的单词，则可以将优先级表作为永久表

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

Answer 3

试试这个：

select *,'Hai' as 'First Matched Word' from Table1 where [Description] like '%Hai%'
union
select *,'Hello' as 'First Matched Word' from Table1 where [Description] like '%hello%' 
and [No] not in(select [No] from Table1 where [Description] like '%Hai%')
union
select *,'Good' as 'First Matched Word' from Table1 where [Description] like '%Good%' 
and [No] not in(select [No] from Table1 where [Description] like '%hello%' 
or [Description] like '%Hai%'
);