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

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.

Answer 2

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 %