mysql Select Like column like column1 OR column2

本文关键字：column1 OR column2 like column Select Like mysql | 更新日期: 2023-09-27 18:04:45

在我的webapp中，我有一个搜索框，这样我就可以使用名字或姓氏搜索我的数据库，它将在我的webapp中显示结果。用户输入名字或姓氏。使用Like query.如何在这个查询中编写Like query.

 public DataTable SearchbyOPDname(string fname, string lname)
   {
       if (con.State == ConnectionState.Closed)
       {
           con.Open();
       }
       string sql = "SELECT opd_id AS [OPD No], opd_date AS DATE, opd_dpt AS DEPARTMENT, 
       opd_pfname AS [FIRST NAME], opd_plname AS [LAST NAME], opd_age AS AGE, opd_gender AS GENDER, 
       opd_mob AS [MOBILE NO], opd_fthrname AS [FATHER NAME], opd_hsbndname AS [HUSBAND NAME] 
       FROM tbl_OPD WHERE opd_pfname like'" + fname +"' OR opd_plname like'" + lname + "'ORDER BY DATE DESC";
       SqlDataAdapter adp = new SqlDataAdapter(sql, con);
       DataTable dt = new DataTable();
       adp.Fill(dt);
       con.Close();
       return dt;
   }

mysql Select Like column like column1 OR column2

我认为你的LIKE部分需要使用%;

WHERE opd_pfname LIKE '%" + fname + @"%' OR opd_plname LIKE '%" + lname + @"%'
  • SQL LIKE Operator
但更重要的是(正如我在评论中提到的)始终使用参数化查询。您的代码是开放的SQL注入攻击。例如,
         WHERE opd_pfname LIKE '%' + @fname + '%'
            OR opd_plname LIKE '%' + @lname + '%'
cmd.Parameters.AddWithValue(@fname, fname);
cmd.Parameters.AddWithValue(@lname, lname);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adp.Fill(dt);

如果您想使用LIKE进行部分匹配，则必须在模式之前和之后包含%符号。这应该可以正常工作:

string sql = @"SELECT opd_id AS [OPD No]
              , opd_date AS DATE
              , opd_dpt AS DEPARTMENT
              , opd_pfname AS [FIRST NAME]
              , opd_plname AS [LAST NAME]
              , opd_age AS AGE
              , opd_gender AS GENDER
              , opd_mob AS [MOBILE NO]
              , opd_fthrname AS [FATHER NAME]
              , opd_hsbndname AS [HUSBAND NAME] 
          FROM tbl_OPD 
         WHERE opd_pfname LIKE '%" + fname + @"%'
            OR opd_plname LIKE '%" + lname + @"%'
         ORDER BY DATE DESC";

作为旁注，您应该使用参数化查询，而不是像那样手动构造查询!

下面是如何对参数化查询执行相同操作的方法: 
  using(SqlCommand cmd = con.CreateCommand())
  {
     cmd.Text = @"SELECT opd_id AS [OPD No]
              , opd_date AS DATE
              , opd_dpt AS DEPARTMENT
              , opd_pfname AS [FIRST NAME]
              , opd_plname AS [LAST NAME]
              , opd_age AS AGE
              , opd_gender AS GENDER
              , opd_mob AS [MOBILE NO]
              , opd_fthrname AS [FATHER NAME]
              , opd_hsbndname AS [HUSBAND NAME] 
          FROM tbl_OPD 
         WHERE opd_pfname LIKE '%' + @fname + '%'
            OR opd_plname LIKE '%' + @lname + '%'
         ORDER BY DATE DESC"
     cmd.Parameters.AddWithValue(@fname, fname);
     cmd.Parameters.AddWithValue(@lname, lname);
     cmd.Prepare();
     SqlDataAdapter adp = new SqlDataAdapter(cmd);
     DataTable dt = new DataTable();
     adp.Fill(dt);
  }
   con.Close();
相关文章：