Searching by splitting a row in multiple columns of an SQL table?

Question

I have a Books table:

 Title Board Class ---------------------------------------- Interactive English CBSE 9 Interactive Math ICSE 10 Hindi CBSE 9 

I have a search text box on an asp.net website. If the user enters "9 CBSE" in the text box, my select request should return

 Title Board Class ----------------------------------------- Interactive English CBSE 9 Hindi CBSE 9 

And if the user enters "9 English" , he must return

 Title Board Class ------------------------------------------ Interactive English CBSE 9 

So, what should my select query match the text box value for all of these three columns?

Answer 1

I can’t verify this because I don’t have access to Sqlserver now, but this should work:

 select * from books where patindex('%' + left(_textbox_contents_, charindex(' ') - 1) + '%', Title + Board + Class) > 0 and patindex('%' + substring(_textbox_contents_, charindex(' ') + 1) + '%', Title + Board + Class) > 0 

Answer 2

If you can break a line in C # before sending a request and create variables containing details, for example:

string1 = "9" string2 = "English"

Then you need to add “%” on each side to give you:

string1 = "% 9%" string2 =% English% "

Then pass the request as:

 Select * from books where (title like string1 or title like string2) or (board like string1 or board like string2) or (class like string1 or class like string2); 

I find that splitting the search string before sending the query is easier than trying to do it using SQL, where you will need to create a bunch of instrings and substrings.

Answer 3

To solve this problem, we must plan how to relate the problem.

• We must learn from the input values for search Title and or Board number, if available.
• We need to build our SQL Query in the order that it matches our extraction from the Title or Board columns.
• We should try to find the Number match in our SQL Query only if the Number actually retrieved (if it is retrieved, it has a value other than null or Whitespace ).

SearchInput: An entity representing the input values ​​of a search.

 public class SearchInput { public string TitleOrBoard { get; set; } public string Number { get; set; } } 

CreateSearchInput: Convert the input string to the above entity.

Search if the first value in the string is a number. If the first value is a number, then assign it to Number and remove the number from the input string.

 public static SearchInput CreateSearchInput(string input) { var inputSplitted = input.Trim().Split(new [] { ' ' }, StringSplitOptions.RemoveEmptyEntries); int number; string searchText = ""; if (int.TryParse(inputSplitted.First(), out number)) { searchText = String.Join(" ", inputSplitted.Skip(1)); } var searchInput = new SearchInput() { Number = number.ToString(), TitleOrBoard = searchText }; return searchInput; } 

ConstructSearchQuery: Build SQLQuery based on SearchInput values.

 public static string ConstructSearchQuery(SearchInput searchInput) { StringBuilder sb = new StringBuilder("SELECT * FROM Books WHERE"); sb.AppendFormat(" ([Title] LIKE '%{0}%' OR [Board] LIKE '%{0}%')", searchInput.TitleOrBoard); if (!String.IsNullOrWhiteSpace(searchInput.Number)) { sb.AppendFormat(" OR Class = '%{0}%'", searchInput.Number); } return sb.ToString(); } 

Main function

 public static void Main() { string input = "9 English"; var searchInput = CreateSearchInput(input); string sqlQuery = ConstructSearchQuery(searchInput); Console.WriteLine("Search: Number[{0}] TitleOrBoard[{1}]", searchInput.Number, searchInput.TitleOrBoard); Console.WriteLine("SQL Query: {0}", sqlQuery); } 

Output:

 Search: Number[9] TitleOrBoard[English] SQL Query: SELECT * FROM Books WHERE ([Title] LIKE '%English%' OR [Board] LIKE '%English%') OR Class = '%9%' 

.NET Fiddle

Answer 4

 try this, declare @t table (Title varchar(100), Board varchar(10), Class int) declare @chk varchar(10)='9 English' insert into @t (Title, Board, Class) values ('Interactive English','CBSE',9), ('Interactive Math','ICSE',10), ('Hindi','CBSE',9) select * from @t where cast(Class as varchar(2)) +' '+Board like '%' + @chk +'%' or Board +' '+cast(Class as varchar(2)) like '%' + @chk +'%' or cast(Class as varchar(2)) +' '+case when charindex(' ', title)=0 then title else REverse(substring(reverse(title),1,charindex(' ', REVERSE(title))-1)) end like '%' +@chk +'%' or case when charindex(' ', title)=0 then title else REverse(substring(reverse(title),1,charindex(' ', REVERSE(title))-1)) end +' '+cast(Class as varchar(2)) like '%' +@chk +'%' 

Answer 5

If you want to find all the results in which any of the search keywords matches at least one value, this should work for you:

 private string BuildQueryString(List<string> keywords) { List<string> numbers = keywords.Where(keyword => keyword.All(Char.IsDigit)) .ToList(); List<string> words = keywords.Except(numbers) .Select(keyword => string.Format("('{0}')", keyword)) .ToList(); var queryBuilder = new StringBuilder(); if (words.Count > 0) { queryBuilder.AppendFormat(@" CREATE TABLE #Keywords ( Keyword NVARCHAR(MAX) ) INSERT INTO #Keywords (Keyword) VALUES {0} ", string.Join(", ", words)); } queryBuilder.AppendFormat(@" SELECT Title, Board, Class FROM Books "); string numbersFilter = string.Format("Class IN ({0})", string.Join(", ", numbers)); string wordsFilter = @"EXISTS (SELECT TOP 1 1 FROM #Keywords WHERE Title LIKE '%' + Keyword + '%' OR Board LIKE '%' + Keyword + '%')"; if (numbers.Count > 0 && words.Count > 0) { queryBuilder.AppendFormat(@" WHERE {0} AND {1}", numbersFilter, wordsFilter); } else if (numbers.Count > 0) { queryBuilder.AppendFormat(@" WHERE {0}", numbersFilter); } else if (words.Count > 0) { queryBuilder.AppendFormat(@" WHERE {0}", wordsFilter); } return queryBuilder.ToString(); } 

Example usage example:

 txtSearch.Text = "CBSE 9 MEH 23 Hey 90 N0 Word1"; List<string> keywords = txtSearch.Text .Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries) .ToList(); string queryString = BuildQueryString(keywords); 

Your query string will return the following query for this search text:

And for the contents of the table that you indicated in the description of the question, the result of this query will give the following result:

Both of these entries correspond to column 9 in the Class column and CBSE column in the Board column. You can change the ANDs and ORs inside the query builder if you need different behavior, but usually this is what you asked for. Hope this helps.

Answer 6

Using a full text query, you can give the end user more flexibility in how they build their search. Also, you don’t need to parse the search string so many times, and the search is usually faster than using LIKE.

1. Create a full text directory and index.

 CREATE FULLTEXT CATALOG [ftcat_Books] WITH ACCENT_SENSITIVITY = OFF GO CREATE FULLTEXT INDEX ON [Books] ([Title] LANGUAGE 'English', [Board] LANGUAGE 'English', [Class] LANGUAGE 'English') KEY INDEX [PK_Books] ON ([ftcat_Books], FILEGROUP [PRIMARY]) WITH (CHANGE_TRACKING = AUTO, STOPLIST = OFF) 

Your Books.Class column must be varchar or nvarchar for this.

I recommend not using stop lists ( STOPLIST = OFF ), so that single digits, for example 9 in your example, are not ignored.

More emphasis on sensitivity (in this example does not use Accent sensitivity)

Learn more about change tracking (This example uses change tracking)

2. Convert user queries to Boolean syntax.

When the user types 9 CBSE , he must be converted to "9" AND "CBSE" . Each term must be surrounded by double quotation marks and separated by an AND. (You can also use the characters OR, NOT, NEAR and wildcards, but I'm not sure if you need them in your case.)

Here is an easy way to convert to Boolean syntax that you need using the .NET regular expression (basically, replace each series of 1+ spaces with " AND " and surround the result with more double quotes):

 // assume searchString = "9 CBSE" string searchStringBoolean = "\"" + Regex.Replace(searchString, @"\s+", "\" AND \"") + "\""; 

Keep in mind that this is a very simple approach. You will need to consider what to do if the user has already typed double quotes or AND in their search bar.

3. Run the full text query.

 -- @SearchStringBoolean is the boolean string created in step 2 SELECT TOP 100 Books.Title, Books.Board, Books.Class FROM ContainsTable(Books, *, @SearchStringBoolean) as FullTextResults join Books on FullTextResults.KEY = Books.Id ORDER BY FullTextResults.RANK desc 

This returns the top 100 matches with the best matches sorted first.