Clever Geek Handbook

SQL LIKE Command not including specific data

My data looks like this:

Name: Roger

Profession: Engineer

Name: Jane

Profession: Engineer

code:

string occ = "software engineers";

My SQL command: (Removed parameterized query to make it clear)

"SELECT * FROM Users WHERE Occupation LIKE '%" + occ + "%'"

However, this does not return Accounts because an extra 's' is added at the end of the occ parameter. If I change the value from "ok" to "software engineer" it will return two users. How can I modify the query so that it includes all data similar to the parameter?

That is, include all users with the occupation of either "software engineer" or "software engineer"

+3


source to share


2 answers


There are several ways to solve this problem. You can't just tell the database to give you any records that "look like this search query".

You are probably better off determining what kind of flexibility you are after.

For this particular example and any others it likes differ only in suffix, you can do

SELECT * FROM Users Where CONTAINS(Occupation,@occ)

So if the actual DB occupation contains the occlusion query value anywhere, it will return that record.



Also note that I used @occ You should never do

"SELECT * FROM Users Where CONTAINS(Occupation," + occ + ")";

The absolute worst case is that the "ok" is actually provided from the user of your application, because it is a huge security vulnerability (SQL Injection)

But even if it is provided in some way from your own code, you must still use SQL parameters to add a value to the @occ parameter. Have a look at sql parameters and how to use them.

0


source


You can try this:



"SELECT * FROM Users WHERE Occupation LIKE '%" + occ + "%' OR " +
  ooc + " like '%' + Occupation + '%'"
0


source






More articles:


All Articles