SQL 多條件查詢
使用逗號間隔多關鍵字查詢及對多個欄位進行搜查，組合條件為"或"。
※逗號前後不可留空白，否則會查出全部的資料
SQL
DECLARE @strKeyword VARCHAR(512);
SET @strKeyword = 'john,howard,lee';
SELECT * FROM v_HRM_Evaluation
JOIN dbo.FN_SPLIT_TBL(@strKeyword,',') tblB
ON name + batchnumber + jobtitles +
CONVERT(varchar(30),joblevel) + workingfunc +
ogno + ogname + ouno + ouname + ename + status +
CONVERT(varchar(30),cdt,120) LIKE '%'+tblB.Value+'%'
WHERE 1=1
FN_SPLIT_TBL
--註：字串切割，用在單一欄位多條件同時查詢，如：(Select * from Table Where name like '%張%' or name like '%陳%' or name like '%王%')
--引用範例(一)：
--select tblA.* from t_HRM_Evaluation tblA join dbo.FN_SPLIT_TBL('張,陳,王',',') tblB on tblA.name like '%'+tblB.Value+'%'
--引用範例(二)：
--SELECT dbo.[FN_SPLIT](Name.Value, 2, '=') as Name,
--dbo.[FN_SPLIT](Age.Value, 2, '=') as Age,
--dbo.[FN_SPLIT](Job.Value, 2, '=') as Job,
--dbo.[FN_SPLIT](Location.Value, 2, '=') as Location,
--dbo.[FN_SPLIT](Add_Date.Value, 2, '=') as Add_Date
--FROM HR data
-- CROSS APPLY FN_SPLIT_TBL(data.Details, '|') Name
-- CROSS APPLY FN_SPLIT_TBL(data.Details, '|') Age
-- CROSS APPLY FN_SPLIT_TBL(data.Details, '|') Job
-- CROSS APPLY FN_SPLIT_TBL(data.Details, '|') Location
-- CROSS APPLY FN_SPLIT_TBL(data.Details, '|') Add_Date
--WHERE Name.Pos = 1 AND Age.Pos = 2 AND Job.Pos = 3 AND Location.Pos = 4 AND Add_Date.Pos = 5
CREATE FUNCTION [dbo].[FN_SPLIT_TBL](@InExp varchar(8000), @Sep varchar(10)) --SELECT * FROM DBO.[FN_SPLIT_TBL]('TEST1,TEST2', ',')
RETURNS @Res TABLE(
Pos int,
Value varchar(max))
AS
BEGIN
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@Sep, @InExp)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @InExp, stop + 1)
FROM Pieces
WHERE stop > 0
)
INSERT INTO @Res
SELECT pn, SUBSTRING(@InExp, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces OPTION (MAXRECURSION 0);
RETURN;
END
FN_SPLIT
--註：無
--引用範例(一)：
--SELECT
--dbo.[FN_SPLIT](data.Name, 2, '=') as Name,
--dbo.[FN_SPLIT](data.Age, 2, '=') as Age,
--dbo.[FN_SPLIT](data.Job, 2, '=') as Job,
--dbo.[FN_SPLIT](data.Location, 2, '=') as Location,
--dbo.[FN_SPLIT](data.Add_Date, 2, '=') as Add_Date
--FROM (
-- SELECT
-- dbo.[FN_SPLIT](Details, 1, '|') as Name,
-- dbo.[FN_SPLIT](Details, 2, '|') as Age,
-- dbo.[FN_SPLIT](Details, 3, '|') as Job,
-- dbo.[FN_SPLIT](Details, 4, '|') as Location,
-- dbo.[FN_SPLIT](Details, 5, '|') as Add_Date
-- FROM HR) data
CREATE FUNCTION [dbo].[FN_SPLIT] ( --SELECT DBO.FN_SPLIT('TEST1 , TEST2', 2, ',')
@s varchar(512),
@i int,
@sep char(1) = ',')
RETURNS varchar(512)
AS
BEGIN
DECLARE @Ret VARCHAR(512);
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT @Ret =
RTRIM(SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END))
FROM Pieces
WHERE pn = @i
RETURN @Ret;
END
