Compare a string database value with more than two words

Question

I have a table of users on my database containing the field "Full Name"

On my tool, I am uploading some files and before inserting the data on the database I am checking whether the user exists for example or not by comparing the full name field on the database with the one on the file (It is the only option, I cannot compare the ids).

My question is : How it is possible to compare those fields when they contains more than two words ?

Sometime the full names are not in order, for example :

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

When there is only two words, it's Ok I work with splits like that :

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;
}

Note Query.ExecuteCommand(sql, "int"); returns "0" when the user does not exists.

So if my function getId() returns "0" I will insert the new user, if it returns something else, that means the user exists and will return the Username_Id

Anyone have an Idea ?

Thanks.

Answer 1

You could do something bad like the below (bad because of potential performance costs):

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

The '%' operator is for wildcards. https://msdn.microsoft.com/en-us/library/ms189454.aspx

I'd parameterize your query as well to prevent SQL injection.