Dynamically add like operators in where clause

Question

Can anyone give me a solution for my problem?

I have a string array of max length. I want to compare all of my string array elements with a single SQL query. How can I do that?

string[] new = searchtext;
select Qid from questions where qdescriptions like string[0],string[1],string[2]

The string array length is not fixed, it's dynamic.

Ex: my search string is "admin login error"

Then I split that into

admin
login
error 

as three parts. My expected result should contain all these three strings in database

Like this

Admin post this;
password change for login;
the error database;

Hope you understand. The results should contain all my search strings in a single search query..

C# code:

 public void searchdetails(string[] searchwords) { 
     SqlConnection con = new SqlConnection(); 
     con.ConnectionString = connection; con.Open(); 

     string[] soldesc = searchwords; 
     int i = 0; 

     if (soldesc.Length == 1) { 
        string query1 = "select Qid from Questions where Qdescription like '% " + soldesc[i] + " %'"; 
     }

     SqlCommand cmds = new SqlCommand(query1, con); cmds.ExecuteNonQuery();

Answer 1

You can do this by dynamically creating your sql query:

 string[] new = searchtext;
 String query = "select Qid from questions";

Write a for loop in your application that loops through your search array: Pseudo code incoming:

For(String searchstring in new){
   if(new.indexof(searchstring) === 0){
      query += " where qdescriptions like " + searchstring;
   }
   else{
   //depending on what you want to do here use OR or AND
      query += " or qdescriptions like " + searchstring;
   }

}

result = query.execute();

Note: this is pseudo code, seeing as you didn't say what programming language etc you are using I can't tell you what the actual syntax for the for loop will be like or how to protect your query from sqlInjection

Your C# code should look something like this:

 public void searchdetails(string[] searchwords) { 
    SqlConnection con = new SqlConnection(); con.ConnectionString = connection; 
    con.Open(); 
    string[] soldesc = searchwords;
    string query1 =  "select Qid from Questions";
    For(int i = 0; i<soldesc.Length;i++){
       if (i == 0) { 
         query1 +=  "where Qdescription like '%" + soldesc[i] + "%'";
       }
       else{
         query1 += " AND Qdescription like '%" + soldesc[i] + "%'";
       }
    }


SqlCommand cmds = new SqlCommand(query1, con); cmds.ExecuteNonQuery();

Answer 2

Try this

declare @searchtext nvarchar(max) = 'abc,def,pqr'

create a function

 CREATE  FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20))
RETURNS @Strings TABLE
(   
  position int IDENTITY PRIMARY KEY,
  value varchar(8000)  
)
AS
BEGIN

DECLARE @index int
SET @index = -1

WHILE (LEN(@text) > 0)
  BEGIN 
    SET @index = CHARINDEX(@delimiter , @text) 
    IF (@index = 0) AND (LEN(@text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@text)
          BREAK 
      END 
    IF (@index > 1) 
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))  
        SET @text = RIGHT(@text, (LEN(@text) - @index)) 
      END 
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
  RETURN
END

Query

select * from yourtable y inner join (select value from 
fn_split(@searchtext,',')) as split on y.qdescriptions  like '%+split.value+%'

Answer 3

In C#, you could generate the query text like this...

public static void Main()
{
    string final = GenerateParameters("tableName", "fieldName", new[] {"admin", "login", " error"});

    // execute query
    // final = "SELECT * FROM tableName WHERE fieldName LIKE '%admin%' OR fieldName LIKE '%login%' OR fieldName LIKE '% error%'"
}

static string GenerateParameters(string tableName, string fieldName, IEnumerable<string> searchTerms)
{            
    string sqlParameters = string.Join(" OR ", searchTerms.Select(x => "{0} LIKE '%{1}%'".FormatWith(fieldName, x)));
    return "SELECT * FROM {0} WHERE ".FormatWith(tableName) + sqlParameters;            
}

public static class StringExtensions
{
    public static string FormatWith(this string value, params object[] args)
    {
        return String.Format(value, args);
    }
}