将字符串数据库值与两个以上的单词进行比较

Question

我有一个用户表在我的数据库中包含字段"全名"

在我的工具上，我正在上传一些文件，在数据库上插入数据之前，我正在检查用户是否存在，例如，通过比较数据库上的全名字段与文件上的字段(这是唯一的选项，我不能比较id)。

我的问题是:当这些字段包含两个以上的单词时，如何比较它们?

有时全名不顺序，例如:

-------------------------------------------------
|        Database       ||         File         |
-------------------------------------------------
| Michael Yves Pierrot  || Pierrot Michael Yves |
| Martin Dupont         || Dupont Martin        |
| Ben Jack Dupont       || Ben Dupont Jack      |
-------------------------------------------------

当只有两个单词时，我可以这样分割:

public string getId()
{
    string result;
    QueryModel Query = new QueryModel();
    string sql = "SELECT Username_Id FROM USERNAME WHERE Full_name = '" 
        + Full_name.Replace("'", "''") + "'";
    result = Query.ExecuteCommand(sql, "int");
    if (result != "0")
    {
        string FullName2 = Full_name.Split(' ')[1] + " " + Full_name.Split(' ')[0];
        sql = "SELECT Username_Id FROM USERNAME WHERE Full_name = '" 
            + FullName2.Replace("'", "''") + "'";
        result = Query.ExecuteCommand(sql, "int");
    }
    return result;
}

说明当用户不存在时，Query.ExecuteCommand(sql, "int");返回"0"。

如果我的函数getId()返回"0"，我将插入新用户，如果它返回其他东西，这意味着用户存在并将返回Username_Id

谁有主意?

谢谢。

Answer 1

您可以像下面这样做一些糟糕的事情(糟糕是因为潜在的性能成本):

SELECT Username_Id 
FROM USERNAME 
WHERE Full_name LIKE '%Michael%' AND Full_name LIKE '%Pierrot%'

'%'操作符用于通配符。https://msdn.microsoft.com/en-us/library/ms189454.aspx

我也会参数化你的查询，以防止SQL注入。

Answer 2

这将返回按字母顺序拆分并重新连接的所有部分。你可以用它创建一个函数并直接在compare

中使用它

DECLARE @tbl TABLE(FullName VARCHAR(100));
INSERT INTO @tbl VALUES('Michael Yves Pierrot'),('Michael Pierrot Yves'),('Martin Dupon'),('Dupont Martin'),('Ben Jack Dupont'),('Ben Dupont Jack');
WITH
NamesSplitByXML AS
(
    SELECT FullName
          ,ROW_NUMBER() OVER(ORDER BY FullName) AS inx
          ,CAST('<root><r>' + REPLACE(tbl.FullName,' ','</r><r>') + '</r></root>' AS XML) AS NameAsXml
    FROM @tbl AS tbl
)
,SortedList AS
(
    SELECT inx, ROW_NUMBER() OVER(ORDER BY inx,x.y.value('.','varchar(max)')) AS inx2, FullName,x.y.value('.','varchar(max)') AS NamePart
    FROM NamesSplitByXML
    CROSS APPLY NameAsXml.nodes('/root/r') AS x(y)
) 
,DistinctInx AS
(
    SELECT DISTINCT inx,FullName FROM SortedList
)
SELECT DistinctInx.inx,FullName,ConcatAlphabetical.SortedName
FROM DistinctInx
CROSS APPLY
(
    SELECT STUFF(
    (
        SELECT TOP 100 PERCENT ' ' + NamePart
        FROM SortedList WHERE DistinctInx.inx=SortedList.inx
        ORDER BY inx2
        FOR XML PATH(''),TYPE
    ).value('.','varchar(max)'),1,1,'')
) AS ConcatAlphabetical(SortedName)

Answer 3

感谢@user1666620，我修改了我的函数，新的代码是:

public string getId()
{
    string result;
    QueryModel Query = new QueryModel();
    string[] fullName = Full_name.Replace("'", "''").Split(' ');
    string fullNameSQL = "";
    for (int i = 0; i < fullName.Count() - 1; i++)
    {
        string fn = "Full_Name LIKE '%" + fullName[i] + "%' ";
        fullNameSQL = (fullNameSQL == "") ? fn : fullNameSQL + " AND " + fn;
    }
    string sql = "SELECT Username_Id FROM USERNAME WHERE " + fullNameSQL;
    result = Query.ExecuteCommand(sql, "int");
    return result;
}