問(wèn)題描述

我想請(qǐng)教一下 SQL Server 中是否有任何函數(shù)允許我對(duì)值列表執(zhí)行部分匹配?

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 ?

需要匹配的整個(gè)字符串將通過(guò)存儲(chǔ)過(guò)程傳入??.

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

在編寫(xiě)自己的函數(shù)之前，我試圖找到其他替代方法，以逗號(hào)分隔字符串，然后在將數(shù)據(jù)返回給程序之前合并所有結(jié)果.

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.

例如，我會(huì)將以下字符串傳入我的 TSQL

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

蘋(píng)果、橙子、梨

在我的 WHERE 子句中它應(yīng)該匹配

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

我可以在單個(gè) SQL 語(yǔ)句中實(shí)現(xiàn)上述結(jié)果而不是編寫(xiě)函數(shù)來(lái)打破每個(gè)字符串嗎?

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

我表中的數(shù)據(jù)

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

所以，當(dāng)我傳入字符串 "apple,pear" 時(shí)，我需要返回

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

apple red
apple green
pear big
pear small

推薦答案

您可以將臨時(shí)表創(chuàng)建為

You can create a temp table as

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

旁注:確保檢查臨時(shí)表是否存在以避免錯(cuò)誤.現(xiàn)在您可以將搜索詞插入到臨時(shí)表中

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

現(xiàn)在使用這個(gè)臨時(shí)表，使用 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
'

請(qǐng)注意，我盡量避免使用臨時(shí)表，但在這里它很方便，而且我使用此解決方案的情況對(duì)性能沒(méi)有要求.相反，它更容易保持不斷增長(zhǎng)的 searchItems 的維護(hù).

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.

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