查找并显示列表中匹配的子字符串

Question

请帮助我获得以下关于SQL的输出。

表AnimalQueries包含名为OrignialString的列。

OriginalString将如下所示：

with cat pain chat to a vet online help dog whisperer ask vet online free hamster treat

我希望输出如下所示

OriginalString           Animal
with cat pain             Cat
chat to a vet online      Vet
help dog whisperer        Dog
ask vet online free       Vet
hamster treat             Hamster

Declare @Animal table (Animal varchar(200))
insert into @Animal(Animal)
    values('dog','cat','vet','hamster')
SELECT OriginalString, Animal from AnimalQueries

Answer 1

我认为应该行得通：

SELECT 
     OriginalString, 
     ( SELECT TOP(1) Animal FROM @Animal WHERE OriginalString LIKE '% ' + Animal  + ' %'  )

FROM AnimalQueries

Answer 2

在ON子句中需要一个join和LIKE运算符：

CREATE TABLE AnimalQueries
    ([OriginalString] varchar(20))
;

INSERT INTO AnimalQueries
    ([OriginalString])
VALUES
    ('with cat pain'),
    ('chat to a vet online'),
    ('help dog whisperer'),
    ('ask vet online free'),
    ('hamster treat')
;

Declare @Animal table (Animal varchar(200))
insert into @Animal(Animal) values('dog'),('cat'),('vet'),('hamster');

select aq.OriginalString, a.Animal
from AnimalQueries aq left join @Animal a
on ' ' + aq.OriginalString + ' ' like '% ' + a.Animal + ' %'

通过连接OriginalString和Animal列前后的空格，可以确保代码在OriginalString中查找的是整个单词，而不是单词的一部分。

请参阅demo。

结果：

> OriginalString       | Animal 
> :------------------- | :------
> with cat pain        | cat    
> chat to a vet online | vet    
> help dog whisperer   | dog    
> ask vet online free  | vet    
> hamster treat        | hamster

Answer 3

您可以对SELECT使用此查询

SELECT *
FROM animallist A
LEFT JOIN  orginalstring O ON CHARINDEX(A.animal, O.orginalstring) > 0