将分隔字符串传递给存储过程以搜索数据库 [英] Passing delimited string to stored procedure to search database
问题描述
如何将用空格或逗号分隔的字符串传递给存储过程并过滤结果?我正在尝试做类似的事情 -
How can i pass a string delimited by space or comma to stored procedure and filter result? I'm trying to do something like -
Parameter Value
--------------------------
@keywords key1 key2 key3
然后是我想要的存储过程
Then is stored procedure i want to first
- 查找所有带有 first 或 last 的记录名称如 key1
- 用第一个或最后一个过滤步骤 1名称如 key2
- 使用名字或姓氏过滤第 2 步，如键 3
另一个例子:
col1 | col2 | col3
------------------------------------------------------------------------
hello xyz | abc is my last name | and i'm a developer
hello xyz | null | and i'm a developer
如果我搜索任何以下内容，它应该为每个返回吗?
If i search for any following it should return for each?
xyz developer"返回 2 行
"xyz developer" returns 2 rows
"xyz abc" 返回 1 行
"xyz abc" returns 1 row
abc developer"返回 1 行
"abc developer"returns 1 row
"hello" 返回 2 行
"hello" returns 2 rows
hello developer"返回 2 行
"hello developer" returns 2 rows
"xyz" 返回 2 行
"xyz" returns 2 rows
推荐答案
由于您不能使用表参数(不在 SQL Server 2008 上)，请尝试传入 CSV 字符串并让存储过程将其拆分为行你.
Since you can't use a table parameter (not on SQL Server 2008), try passing in a CSV sting and have the stored procedure split it into rows for you.
在 SQL Server 中有多种拆分字符串的方法.本文涵盖了几乎所有方法的优点和缺点:
There are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method:
"SQL Server 2005 及更高版本中的数组和列表，当表值参数时不要剪掉它"作者:Erland Sommarskog
您需要创建一个拆分函数.拆分函数的使用方法如下:
You need to create a split function. This is how a split function can be used:
SELECT
*
FROM YourTable y
INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value
我更喜欢使用数字表方法在 TSQL 中拆分字符串 但在 SQL Server 中有多种拆分字符串的方法，请参阅上一个链接，其中解释了每种方法的优点和缺点.
I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.
要使 Numbers Table 方法起作用，您需要进行一次时间表设置，这将创建一个表 Numbers，其中包含从 1 到 10,000 的行:
For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
设置 Numbers 表后，创建此拆分函数:
Once the Numbers table is set up, create this split function:
CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN
( ----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''
);
GO
您现在可以轻松地将一个空格分隔的字符串拆分成一个表格并加入它或根据需要使用它此代码基于 OPs 最新问题
You can now easily split a space delimited string into a table and join on it or use it however you need This codes is based on the OPs latest question edit:
CREATE TABLE YourTable (PK int, col1 varchar(20), col2 varchar(20), col3 varchar(20))
--data from question
INSERT INTO YourTable VALUES (1,'hello xyz','abc is my last name','and i''m a developer')
INSERT INTO YourTable VALUES (2,'hello xyz',null,'and i''m a developer')
CREATE PROCEDURE YourProcedure
(
@keywords varchar(1000)
)
AS
SELECT
@keywords AS KeyWords,y.*
FROM (SELECT
t.PK
FROM dbo.FN_ListToTable(' ',@keywords) dt
INNER JOIN YourTable t ON t.col1 LIKE '%'+dt.ListValue+'%' OR t.col2 LIKE '%'+dt.ListValue+'%' OR t.col3 LIKE '%'+dt.ListValue+'%'
GROUP BY t.PK
HAVING COUNT(t.PK)=(SELECT COUNT(*) AS CountOf FROM dbo.FN_ListToTable(' ',@keywords))
) dt
INNER JOIN YourTable y ON dt.PK=y.PK
GO
--from question
EXEC YourProcedure 'xyz developer'-- returns 2 rows
EXEC YourProcedure 'xyz abc'-- returns 1 row
EXEC YourProcedure 'abc developer'-- returns 1 row
EXEC YourProcedure 'hello'-- returns 2 rows
EXEC YourProcedure 'hello developer'-- returns 2 rows
EXEC YourProcedure 'xyz'-- returns 2 rows
输出:
KeyWords PK col1 col2 col3
-------------- ----- ---------- -------------------- --------------------
xyz developer 1 hello xyz abc is my last name and i'm a developer
xyz developer 2 hello xyz NULL and i'm a developer
(2 row(s) affected)
KeyWords PK col1 col2 col3
-------------- ----- ---------- -------------------- --------------------
xyz abc 1 hello xyz abc is my last name and i'm a developer
(1 row(s) affected)
KeyWords PK col1 col2 col3
-------------- ----- ---------- -------------------- --------------------
abc developer 1 hello xyz abc is my last name and i'm a developer
(1 row(s) affected)
KeyWords PK col1 col2 col3
-------------- ----- ---------- -------------------- --------------------
hello 1 hello xyz abc is my last name and i'm a developer
hello 2 hello xyz NULL and i'm a developer
(2 row(s) affected)
KeyWords PK col1 col2 col3
--------------- ----- ---------- -------------------- --------------------
hello developer 1 hello xyz abc is my last name and i'm a developer
hello developer 2 hello xyz NULL and i'm a developer
(2 row(s) affected)
KeyWords PK col1 col2 col3
-------------- ----- ---------- -------------------- --------------------
xyz 1 hello xyz abc is my last name and i'm a developer
xyz 2 hello xyz NULL and i'm a developer
(2 row(s) affected)
这篇关于将分隔字符串传递给存储过程以搜索数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
