怀疑LIKE的SQL查询 [英] Doubt about SQL query with LIKE

问题描述

您好，



如果有人可以提前感谢，我对我需要的SQL查询有疑问。



我想通过C＃中的应用程序查询在MS Acess中创建的产品数据库。我需要咨询数据库，通过引用查找产品，这个引用可能包含各种名称和空格，例如：ABC FFF HHH XXXXXXXX，我想例如在文本框中引入要搜索的字符串部分，例如： HHH和查询将返回包含HHH的所有引用，无论是在引用的开头，中间还是末尾。



我有的查询不返回任何内容：

Hello,

I have a doubt about a sql query that i need if anyone can help thanks in advance.

I want to query a products database created in MS Acess through an application in C #. I need to consult the database looking for the product by reference, this reference may contain various names and spaces, for example: "ABC FFF HHH XXXXXXXX" and i want for example to introduce in textbox part of a string to search, for example: "HHH" and the query will return me all references containing "HHH" regardless of being at the beginning, middle or end of the reference.

The query that i have does not return anything:

Select * from product where product_ref like '% + TextBox_ref.Text +%';

我应该在查询中更改以获取包含在TextBox中输入的字符的所有引用，而不管数据库中引用可能包含的空格？



谢谢s，



我的尝试：

What should i change in the query to get all the references containing the characters entered in the TextBox regardless of spaces that the reference may have in the database?

Thanks,

What I have tried:

Select * from product where product_ref like '% + TextBox_ref.Text +%';

推荐答案

正如已经指出的那样，您应该将文本框中的值连接到单独的字符串，例如

As already pointed out, you should concatenate the value from the text box to separate strings, for example

Select * from product where product_ref like '%'" + TextBox_ref.Text + "'%'

然而，你真正应该做的是使用参数， OleDbParameter类（System.Data.OleDb） [ ^ ] in这个案例。然后查询文本看起来像

However, what you really should do is to use paramters, OleDbParameter Class (System.Data.OleDb)[^] in this case. The query text would then look like

Select * from product where product_ref like '%' + @searchString + '%'

现在您要为OleDbCommand添加一个参数并设置它的价值。这样可以保护您免受SQL注入的影响。



不确定是否还要查找 HHH 的行，即使数据库中的数据是 H HH 。如果是这种情况，您可以使用REPLACE函数删除空格。例如

Now you would add a parameter for the OleDbCommand and set it's value. This would keep you safe from for example SQL injections.

Not sure if you wanted also to find rows having HHH even if the data in the database would be H HH. If that is the case, you can use REPLACE function to remove whitespace. For example

Select * from product where REPLACE(product_ref, ' ', '') like '%' + @searchString + '%'

这篇关于怀疑LIKE的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！