SQL Customized search with special characters

SQL Customized search with special characters

I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. I know a relational database engine is designed from the principle that a cell holds a single value and obeying to this rule can help for performance.But in this case table is already running and have millions of data and can't change the table structure.

Take a look on the example what I exactly want to do is

I have a main table name tbl_main in SQL

AS_ID   KWD  1   Man,Businessman,Business,Office,confidence,arms crossed  2   Man,Businessman,Business,Office,laptop,corridor,waiting  3   man,business,mobile phone,mobile,phone  4   Welcome,girl,Greeting,beautiful,bride,celebration,wedding,woman,happiness  5   beautiful,bride,wedding,woman,girl,happiness,mobile phone,talking  6   woman,girl,Digital Tablet,working,sitting,online  7   woman,girl,Digital Tablet,working,smiling,happiness,hand on chin   

If search text is = Man,Businessman then result AS_ID is =1,2

If search text is = Man,-Businessman then result AS_ID is =3

If search text is = woman,girl,-Working then result AS_ID is =4,5

If search text is = woman,girl then result AS_ID is =4,5,6,7

What is the best why to do this, Help is much appreciated.Thanks in advance

Answer by CJBS for SQL Customized search with special characters

From what you've described, you want the keywords that are included in the search text to be a match in the KWD column, and those that are prefixed with a - to be excluded.

Despite the data existing in this format, it still makes most sense to normalize the data, and then query based on the existence or non existence of the keywords.

To do this, in very rough terms:-

1. Create two additional tables - Keyword and tbl_Main_Keyword. Keyword contains a distinct list of each of the possible keywords and tbl_Main_Keyword contains a link between each record in tbl_Main to each Keyword record where there's a match. Ensure to create an index on the text field for the keyword (e.g. the Keyword.KeywordText column, or whatever you call it), as well as the KeywordID field in the tbl_Main_Keyword table. Create Foreign Keys between tables.
2. Write some DML (or use a separate program, such as a C# program) to iterate through each record, parsing the text, and inserting each distinct keyword encountered into the Keyword table. Create a relationship to the row for each keyword in the tbl_main record.
3. Now, for searching, parse out the search text into keywords, and compose a query against the tbl_Main_Keyword table containing both a WHERE KeywordID IN and WHERE KeywordID NOT IN clause, depending on whether there is a match.

Take note to consider whether the case of each keyword is important to your business case, and consider the collation (case sensitive or insensitive) accordingly.

Answer by cha for SQL Customized search with special characters

I think you can easily solve this by creating a FULL TEXT INDEX on your KWD column. Then you can use the CONTAINS query to search for phrases. The FULL TEXT index takes care of the punctuation and ignores the commas automatically.

-- If search text is = Man,Businessman then the query will be  SELECT AS_ID FROM tbl_main  WHERE CONTAINS(KWD, '"Man" AND "Businessman"')    -- If search text is = Man,-Businessman then  the query will be  SELECT AS_ID FROM tbl_main  WHERE CONTAINS(KWD, '"Man" AND NOT "Businessman"')    -- If search text is = woman,girl,-Working  the query will be  SELECT AS_ID FROM tbl_main  WHERE CONTAINS(KWD, '"woman" AND "girl" AND NOT "working"')  

To search the multiple words (like the mobile phone in your case) use the quoted phrases:

SELECT AS_ID FROM tbl_main  WHERE CONTAINS(KWD, '"woman" AND "mobile phone"')  

As commented below the quoted phrases are important in all searches to avoid bad searches in the case of e.g. when a search term is "tablet working" and the KWD value is woman,girl,Digital Tablet,working,sitting,online

There is a special case for a single - search term. The NOT cannot be used as the first term in the CONTAINS. Therefore, the query like this should be used:

-- If search text is = -Working  the query will be  SELECT AS_ID FROM tbl_main  WHERE NOT CONTAINS(KWD, '"working"')  

Answer by Hosein for SQL Customized search with special characters

I would prefer cha's solution, but here's another solution:

declare @QueryParts table (q varchar(1000))  insert into @QueryParts values  ('woman'),  ('girl'),  ('-Working')    select AS_ID  from tbl_main  inner join @QueryParts on  (q not like '-%' and ',' + KWD + ',' like '%,' + q + ',%') or  (q like '-%' and ',' + KWD + ',' not like '%,' + substring(q, 2, 1000) + ',%')  group by AS_ID  having COUNT(*) = (select COUNT(*) from @QueryParts)  

Answer by Engineeration for SQL Customized search with special characters

With such a design, you would have two tables. One that defines the IDs and a subtable that holds the set of keywords per search string.

Likewise, you would transform the search strings into two tables, one for strings that should match and one for negated strings. Assuming that you put this in a stored procedure, these tables would be table-value parameters.

Once you have this set up, the query is simple to write:

SELECT M.AS_ID  FROM   tbl_main M  WHERE  (SELECT COUNT(*)          FROM   tbl_keywords K          WHERE  K.AS_ID = M.AS_ID            AND  K.KWD IN (SELECT word FROM @searchwords)) =         (SELECT COUNT(*) FROM @searchwords)    AND  NOT EXISTS (SELECT *                     FROM   tbl_keywords K                     WHERE  K.AS_ID = M.AS_ID                       AND  K.KWD IN (SELECT word FROM @minuswords))  

Answer by Felix Pamittan for SQL Customized search with special characters

Here is my attempt using Jeff Moden's DelimitedSplit8k to split the comma-separated values.

First, here is the splitter function (check the article for updates of the script):

CREATE FUNCTION [dbo].[DelimitedSplit8K](      @pString VARCHAR(8000), @pDelimiter CHAR(1)  )  RETURNS TABLE WITH SCHEMABINDING AS  RETURN  WITH E1(N) AS (      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL       SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1  )  ,E2(N) AS (SELECT 1 FROM E1 a, E1 b)  ,E4(N) AS (SELECT 1 FROM E2 a, E2 b)  ,cteTally(N) AS(      SELECT TOP (ISNULL(DATALENGTH(@pString), 0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4  )  ,cteStart(N1) AS(      SELECT 1 UNION ALL       SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString, t.N, 1) = @pDelimiter  ),  cteLen(N1, L1) AS(  SELECT       s.N1,      ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pString, s.N1),0) - s.N1, 8000)  FROM cteStart s  )  SELECT       ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),      Item       = SUBSTRING(@pString, l.N1, l.L1)  FROM cteLen l  

Here is the complete solution:

-- search parameter  DECLARE @search_text VARCHAR(8000) = 'woman,girl,-working'    -- split comma-separated search parameters  -- items starting in '-' will have a value of 1 for exclude  DECLARE @search_values TABLE(ItemNumber INT, Item VARCHAR(8000), Exclude BIT)  INSERT INTO @search_values  SELECT       ItemNumber,       CASE WHEN LTRIM(RTRIM(Item)) LIKE '-%' THEN LTRIM(RTRIM(STUFF(Item, 1, 1 ,''))) ELSE LTRIM(RTRIM(Item)) END,      CASE WHEN LTRIM(RTRIM(Item)) LIKE '-%' THEN 1 ELSE 0 END  FROM dbo.DelimitedSplit8K(@search_text, ',') s    ;WITH CteSplitted AS( -- split each KWD to separate rows      SELECT *      FROM tbl_main t      CROSS APPLY(          SELECT              ItemNumber, Item = LTRIM(RTRIM(Item))          FROM dbo.DelimitedSplit8K(t.KWD, ',')      )x    )  SELECT       cs.AS_ID  FROM CteSplitted cs  INNER JOIN @search_values sv      ON sv.Item = cs.Item  GROUP BY cs.AS_ID  HAVING      -- all parameters should be included (Relational Division with no Remainder)      COUNT(DISTINCT cs.Item)  = (SELECT COUNT(DISTINCT Item) FROM @search_values WHERE Exclude = 0)      -- no exclude parameters      AND SUM(CASE WHEN sv.Exclude = 1 THEN 1 ELSE 0 END) = 0  

SQL Fiddle

This one uses a solution from the Relational Division with no Remainder problem discussed in this article by Dwain Camps.

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72