Searching in SQL Multiple Keyword

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