Compare words and get exact match

Question

Now this is tricky... I hope i can explain it well..

I have a a table which names in it and i need to compare it with the word i provide and get the exact match out..

Now i say it is tricky because i tried this query and it gave me a set of names. It contained the word which was an exact match and also words which were similar...

this is what i did:

DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = ConfigurationManager.ConnectionStrings["xyz "].ConnectionString;
        connection.Open();
        SqlCommand sqlCmd = new SqlCommand("SELECT Names FROM Machines WHERE Name  Like '%' + @userName + '%'", connection);
        SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

        sqlCmd.Parameters.AddWithValue("@userName", userName);

        sqlDa.Fill(dt);
        connection.Close();

Now what i want is... for example.

if the database has three words

abc123

xyz123

pqc1238

Now if the word is 123 it should return abc123 and xyz123 if c123 then return abc123 and NOT pqc1238 and xyz123

if 238 then just return pqc1238....

I hope i was clear enough... trust me i did not get it the first time either

any suggestions are better to fingd the exact matches between words.. coz LIKE is not sufficient..

Thanks

Answer 1

From what I understand, it seems you only want string ending with the search pattern, so all you have to do is omit the last "%" from your select.

SqlCommand sqlCmd = new SqlCommand("SELECT Names FROM Machines WHERE Name  Like '%" + @userName + "'", connection);

Answer 2

To me, it looks like you are search for string ending with @userName.

SqlCommand sqlCmd = new SqlCommand("SELECT Names FROM Machines WHERE Name  Like '%' + @userName", connection);

Answer 3

I wouldn't call this EXACT match, but if you are looking for words ending with a given word then try:

"SELECT Names FROM Machines WHERE Name  Like '%' + @userName"

no second %

Answer 4

It reads as though you want a command of the form select Names from Machines where Names like '%xxx', where "xxx" is your word

If that's the case, and @userName is your word then you want something like

SqlCommand sqlCmd = new SqlCommand("SELECT Names FROM Machines WHERE Name  Like '%' + @userName", connection);

Answer 5

Why don't you try using LINQ? Do something like;

var query = from c in db.Machines where c.Name.EndsWith("123") select c.Names;

I hope this helps.

Answer 6

It seems to me that you want all the records that ends with the specific word. In that case, yo just need to remove the last '%' you add to the query.

SqlCommand sqlCmd = new SqlCommand("SELECT Names FROM Machines WHERE Name  Like '%' + @userName", connection);

Hope that's what you're needing

Answer 7

You have it almost right but you're not understanding the % wildcard.

In SQL the % wildcard will match any string of any length. You want that at the beginning because you don't care about the leading parts.

However, when you put it on the end of your LIKE matching string it will allow a match if anything is coming after your string.

So, taking your example

abc123

xyz123

pqc1238

Now if the word is 123 it should return abc123 and xyz123 if c123 then return abc123 and >NOT pqc1238 and xyz123

if 238 then just return pqc1238....

You have %123% you are saying "I want all strings which begin with anything, then have '123', and then end with anything." This will match any string which contains '123' at any point.

What you want to say is "I want all strings which begin with anything, then have '123', and then end." So, that is %123.

Finally, to get an EXACT match with like, just leave off any wildcards.

This page has a pretty good overview of SQL wildcards.