SQL SERVER 2005 中的多重搜索选项 [英] Multiple Search Option in SQL SERVER 2005
本文介绍了SQL SERVER 2005 中的多重搜索选项的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个要求.
假设Test1,Test2,我必须执行类似的操作.
Given say Test1,Test2, I have to perform a like operation.
类似的东西
select * from tblname where column_name like('Test1%','Test2%');
即这些字符串以逗号分隔
i.e. these strings are comma separated
我该如何解决这个问题?
How do I solve this?
这是在 SQL SERVER 2005 中.
This is in SQL SERVER 2005.
提前致谢
推荐答案
Sql server 2005,试试这个
Sql server 2005, try this
--Lookup Table
DECLARE @Values TABLE(
Column_Name VARCHAR(MAX)
)
INSERT INTO @Values (Column_Name) SELECT 'A'
INSERT INTO @Values (Column_Name) SELECT 'B'
INSERT INTO @Values (Column_Name) SELECT 'ATADA'
INSERT INTO @Values (Column_Name) SELECT 'TADAA'
INSERT INTO @Values (Column_Name) SELECT 'Test123A'
INSERT INTO @Values (Column_Name) SELECT '1Test123A'
--Lookup string and delim
DECLARE @LookupString VARCHAR(MAX)
DECLARE @Delim VARCHAR(1)
SET @LookupString = 'Test1,Test2,TADA'
SET @Delim = ',';
--CREATE A LOOKUP TABLE FOR SPLIT STRINGS
WITH substrings (Val, Remainder) AS(
SELECT CASE WHEN CHARINDEX(@Delim,@LookupString) = 0 THEN @LookupString ELSE LEFT(@LookupString,CHARINDEX(@Delim,@LookupString)-1) END,
CASE WHEN CHARINDEX(@Delim,@LookupString) = 0 THEN '' ELSE RIGHT(@LookupString,LEN(@LookupString) - CHARINDEX(@Delim,@LookupString)) END
UNION ALL
SELECT CASE WHEN CHARINDEX(@Delim,Remainder) = 0 THEN Remainder ELSE LEFT(Remainder,CHARINDEX(@Delim,Remainder)-1) END,
CASE WHEN CHARINDEX(@Delim,Remainder) = 0 THEN '' ELSE RIGHT(Remainder,LEN(Remainder) - CHARINDEX(@Delim,Remainder)) END
FROM substrings
WHERE CHARINDEX(@Delim,Remainder) >= 0 AND Val != '' AND Remainder != ''
)
SELECT v.Column_Name,
substrings.Val
FROM @Values v INNER JOIN
substrings ON v.Column_Name LIKE substrings.Val + '%'
这篇关于SQL SERVER 2005 中的多重搜索选项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文