TSQL - 对多个值使用 LIKE 的部分匹配

2021-09-10 00:00:00 sql tsql sql-server

我想请教一下 SQL Server 中是否有任何函数允许我对值列表执行部分匹配?

I would like to seek your advice whether any function in SQL server that allow me to perform partial matching for a list of values ?

需要匹配的整个字符串将通过存储过程传入​​.

The entire string that need to be matched will be passed in via store procedure.

在编写自己的函数之前，我试图找到其他替代方法，以逗号分隔字符串，然后在将数据返回给程序之前合并所有结果.

I am trying to find other alternative before writing my own function to split the string by comma and then union all the results before return the data to the program.

例如，我会将以下字符串传入我的 TSQL

For example, I would pass in the following string into my TSQL

苹果、橙子、梨

在我的 WHERE 子句中它应该匹配

in my WHERE clause it should match

select * from store where fruits like 'apple%'
select * from store where fruits like 'orange%'
select * from store where fruits like 'pear%'

我可以在单个 SQL 语句中实现上述结果而不是编写函数来打破每个字符串吗?

Can I achieve the above results in a single SQL statement rather than writing function to break each string ?

我表中的数据

apple red
apple green
orange sweet
orange sour
pear big
pear small

所以，当我传入字符串 "apple,pear" 时，我需要返回

So, when I passed in the string "apple,pear" , I need to return 

apple red
apple green
pear big
pear small

推荐答案

您可以将临时表创建为

You can create a temp table as 

'CREATE TABLE #Pattern (
      SearchItems VARCHAR(20)
    );'

旁注:确保检查临时表是否存在以避免错误.现在您可以将搜索词插入到临时表中

Side note: Make sure you check if the temp table exists to avoid errors. Now you can insert your search words to the temp table as

'INSERT 
    INTO #Pattern 
    VALUES 
        ('% APPLE %'),
        ('% ORANGE %'),
        ('% BANANA %');'

现在使用这个临时表，使用 INNER JOIN 搜索你的表喜欢

Now using this temp table, Search your table using a INNER JOIN like 

'SELECT *
 FROM Store
 INNER JOIN #Pattern
    ON Store.Fruits LIKE SearchItems
'

请注意，我尽量避免使用临时表，但在这里它很方便，而且我使用此解决方案的情况对性能没有要求.相反，它更容易保持不断增长的 searchItems 的维护.

As a note, Temp Tables are something I try to avoid mostly, but here it comes handy, and the case I was using this solution was not demanding on performance. Rather it made it easier to keep the ever growing searchItems maintained.

希望这也适用于其他人.

Hope this works for others too.

相关文章