問題描述

我想請教一下 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.

這篇關于TSQL - 對多個值使用 LIKE 的部分匹配的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！