sql - SQL中根据 "First Name"+ "Last Name"过滤行

Question

我的数据库中有一个用户表，其中包含 FirstName 和 LastName 两列。现在在我的前端有一个文本框可以从这个表中过滤掉用户。假设我以输入参数“@SEARCHKEYWORD”的形式从前端获取该输入。我在下面创建了一个示例:

DECLARE @Test TABLE
([ID] INT IDENTITY,
[FNAME] NVARCHAR(100),
[LNAME] NVARCHAR(100)
)
INSERT INTO @Test( FNAME, LNAME )
SELECT 'John','Resig' UNION ALL
SELECT 'Dave','Ward' UNION ALL
SELECT 'Peter','Smith' UNION ALL
SELECT 'Dave','Smith' UNION ALL
SELECT 'Girija','Acharya'  UNION ALL
SELECT  'Devendra', 'Gujel' UNION ALL
SELECT 'Arjit', 'Gupta'

DECLARE @SEARCHKEYWORD NVARCHAR(100)

SELECT * FROM @Test WHERE FNAME +' '+ LNAME LIKE @SEARCHKEYWORD

即到目前为止，我已经想到了这个查询来过滤掉行，但它没有给出想要的结果:

 
SELECT * FROM @Test WHERE FNAME +' '+ LNAME LIKE @SEARCHKEYWORD

以下是我需要的以下输入所需的输出:

<p>--WHEN  @SEARCHKEYWORD='John Resig'
--Desired OUTPUT: the row which contains 'John','Resig' </p>

<p>--WHEN  @SEARCHKEYWORD='Ac'
--Desired OUTPUT: the row which contains 'Girija','Acharya' </p>

<p>--WHEN  @SEARCHKEYWORD='Smith'
--Desired OUTPUT: the row which contains 'Peter','Smith' and 'Dave','Smith'</p>

<p>--WHEN  @SEARCHKEYWORD='g'
--Desired OUTPUT: the row which contains  'Devendra', 'Gujel' and 'Arjit', 'Gupta'  </p>

<p>--WHEN  @SEARCHKEYWORD='Smith'
--Desired OUTPUT: the row which contains 'Peter','Smith' and 'Dave','Smith'</p>

<p></p>

Answer 1

把它们放在一起

SELECT * FROM @Test WHERE (FName like '%' + @SEARCHKEYWORD + '%') OR (LName like '%' + @SEARCHKEYWORD + '%') OR (FName + ' ' + LName like '%' + @SEARCHKEYWORD + '%')