C# 如何在 SQL 中组合名字和姓氏并使用 LIKE 进行搜索

how to combine firstname and lastname in SQL and search with LIKE

提问by patel.milanb

I would like to combine FirstNameand LastNameinto one column called 'FULL NAME'and search with LIKE in SQL.

我想将FirstName和LastName合并到一列中，'FULL NAME'并在 SQL 中使用 LIKE 进行搜索。

Example 1:

示例 1：

FirstName : Milan
LastName: Patel

FullName: Milan Patel

名字：米兰
姓氏：帕特尔

全名：米兰帕特尔

Search with: Like '%SomeText%'

搜索： Like '%SomeText%'

Example 2:

示例 2：

Title: " Meetings: A practical alternative to work."

标题：“会议：一种实用的工作替代方案。”

Search: I would like to search the entire word within the give string. ex: work should return the above title.

搜索：我想搜索给定字符串中的整个单词。例如：工作应返回上述标题。

Here is what I have so far.

这是我到目前为止所拥有的。

SELECT 
    CE.Title, CC.FirstName + ' ' + CC.LastName AS FullName
FROM 
    EntryTable CE
JOIN 
    UserTable CC ON CE.EntryId = CC.USerId
WHERE 
    CC.FirstName LIKE 'n%'

EDIT

编辑

I am getting it there slowly. search is working fine with this query.

我正在慢慢地得到它。搜索与此查询工作正常。

    SELECT 
     CE.Title 
    FROM 
     EntryTable CE
    JOIN 
     UserTable CC
    ON 
     CE.EntryId = CC.USerId
    WHERE 
     CC.FirstName  + ' ' + CC.LastName LIKE 's%'

BUT it only search for name starting with 's', i would like to search name starting, ending, contains conditions as well. how can i go about that. please help

但它只搜索以 开头的名称's'，我想搜索名称的开头、结尾、包含条件。我该怎么办。请帮忙

采纳答案by Damith

You can use LIKEwith concatenated column values as below

您可以使用LIKE连接的列值，如下所示

WHERE 
  CC.FirstName + ' ' + CC.LastName LIKE 's%'

BUT it only search for name starting with 's', i would like to search name starting, ending, contains conditions as well. how can i go about that. please help

但它只搜索以“s”开头的名字，我想搜索名字的开头、结尾、包含条件。我该怎么办。请帮忙

Then you have to use %before and after search string like below

然后你必须使用%前后搜索字符串，如下所示

WHERE 
  CC.FirstName + ' ' + CC.LastName LIKE '%s%'

回答by Alexander

Combine them in the WHERE clause as well, with two % signs.

也将它们组合在 WHERE 子句中，并使用两个 % 符号。

回答by echo_Me

you can use concat

你可以使用 concat

   SELECT CE.Title, concat(CC.FirstName , ' ' , CC.LastName) AS FullName
   FROM EntryTable CE
  JOIN UserTable CC
  ON CE.EntryId = CC.USerId
  WHERE 
  concat(CC.FirstName , ' ' , CC.LastName) LIKE 'n%'
  OR  CC.FirstName LIKE 'n%' OR  CC.FirstName LIKE '%n'
  OR  CC.LastName LIKE 'n%'  OR CC.LastName LIKE '%n'
                        ^^--// (1).   ^^--// (2) 

(1) to find lastname in first of the string you search .

(1) 在您搜索的字符串的第一个中找到姓氏。

(2) to find lastname in the end of the string you search.

(2) 在你搜索的字符串的末尾找到lastname。

(3)to find last name in middle of string do this '%n%'

(3) 要在字符串中间找到姓氏，请执行此 '%n%'

回答by John Woo

you can't use the ALIASon the WHEREclause because in the SQL Order of Operation, the WHEREclause is executed before the SELECTclause.

不能使用ALIASonWHERE子句，因为在 SQL Order of Operation 中，WHERE子句在SELECT子句之前执行。

better use the complete concatenation is the WHEREclause

更好地使用完整的连接是WHERE子句

WHERE CC.FirstName + ' ' + CC.LastName LIKE 'n%'

if you don't want to use that, wrap your query in a subquery so you can use the ALIAS,

如果您不想使用它，请将您的查询包装在子查询中，以便您可以使用ALIAS,

SELECT *
FROM
(
   SELECT CE.Title, CC.FirstName + ' ' + CC.LastName AS FullName
   FROM   EntryTable CE JOIN UserTable CC
          ON CE.EntryId = CC.USerId
) sub
WHERE sub.FullName LIKE 'n%'

回答by Rudie

SELECT some_column_name FROM some_table where firstname + ' ' + lastname LIKE 'some_value'

no FullNamecolumn needed.

不需要FullName列。

回答by Carlo Guevarra

Where CONCAT(CC.FirstName, ' ', CC.LastName) Like "%n%"

其中 CONCAT(CC.FirstName, ' ', CC.LastName) 像 "%n%"