MS SQL Server
  1. MS SQL Server
  3. Recherche personnalisée SQL avec des caractères spéciaux
Intereting Posts
Est-il préférable d'utiliser des procédures stockées à la place des instructions SQL dans une application C? Comment find une colonne inutilisée dans une database (SQL Server 2008). Dupliquer la vérification en utilisant SELECT ou s'appuyer sur un index unique? Comment get l'logging de filter dans la fonction ROW_NUMBER () et Union? Évitez de répéter le fragment SQL dans la requête? Pourquoi la précision diminue quand on multiplie la sum par un autre nombre Boucle dans le jeu de résultats SELECT dans SQL Éditeur de type access pour SQL Compact Edition Échec de la sauvegarde de la restauration SQL Server 2014 Utilisation de l'instruction – est-ce plus utile pour la connection SQL que l'adaptateur SQL? Ms Sql Convertir et insert entre 2 bases de données Convertir la colonne de bits en entier Comment puis-je découper les loggings dans SQL Server 2005? Comment convertir la date et l'heure dans SQL Server Procédure stockée SQL Server, sélection des lignes de plusieurs tables

Recherche personnalisée SQL avec des caractères spéciaux

Je crée un module de mots-keys où je veux searchr des données en utilisant les mots séparés par des virgules. Et la search est classée en virgule , et less - . Je sais qu'un moteur de database relationnelle est conçu à partir du principe qu'une cellule contient une seule valeur et obéir à cette règle peut aider à la performance. Mais dans ce cas, la table est déjà en cours d'exécution et contient des millions de données.

Jetez un oeil sur l'exemple de ce que je veux exactement faire est

J'ai un nom de table principal tbl_main dans 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

Si le text recherché est = Homme, Homme d'affaires puis résultat AS_ID est = 1,2

Si le text de search est = Man, -Businessman, alors le résultat AS_ID est = 3

Si le text recherché est = femme, fille, -Travail puis résultat AS_ID = = 4,5

Si le text recherché est = femme, fille alors résultat AS_ID est = 4,5,6,7

Quel est le meilleur pourquoi faire cela, l'aide est très appréciée. Merci d'avance

Voici ma tentative d'utiliser DelimitedSplit8k de Jeff Moden pour séparer les valeurs séparées par des virgules.

Tout d'abord, voici la fonction splitter (consultez l'article pour les mises à jour du script) : 

 CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pSsortingng 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(@pSsortingng), 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(@pSsortingng, tN, 1) = @pDelimiter ), cteLen(N1, L1) AS( SELECT s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pSsortingng, s.N1),0) - s.N1, 8000) FROM cteStart s ) SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1), Item = SUBSTRING(@pSsortingng, l.N1, l.L1) FROM cteLen l

Voici la solution complète: 

 -- 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

Celui-ci utilise une solution de la Division Relationnelle sans problème Restant discuté dans cet article par Dwain Camps.

Je pense que vous pouvez facilement résoudre ce problème en créant un INDEX FULL TEXT sur votre colonne KWD . Vous pouvez ensuite utiliser la requête CONTAINS pour searchr des expressions. L'index FULL TEXT prend en charge la ponctuation et ignore automatiquement les virgules. 

 -- 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"')

Pour searchr les mots multiples (comme le mobile phone dans votre cas), utilisez les expressions citées: 

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

Comme commenté ci-dessous les phrases citées sont importantes dans toutes les searchs pour éviter les mauvaises searchs dans le cas par exemple quand un terme de search est "tablette travaillant" et la valeur KWD est woman,girl,Digital Tablet,working,sitting,online

Il existe un cas particulier pour un seul terme de search. Le NOT ne peut pas être utilisé comme premier terme dans le CONTAINS. Par conséquent, la requête comme ceci devrait être utilisée: 

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

D'après ce que vous avez décrit, vous voulez que les mots-keys inclus dans le text de search correspondent dans la colonne KWD , et ceux qui ont un préfixe - à exclure.

Malgré datatables existantes dans ce format, il est toujours plus logique de normaliser datatables, puis d'interroger en fonction de l'existence ou non de ces mots-keys.

Pour ce faire, en termes très approximatifs: –

  1. Créez deux tables supplémentaires – Keyword et tbl_Main_Keyword . Keyword – Keyword contient une list distincte de chacun des mots-keys possibles et tbl_Main_Keyword contient un lien entre chaque logging dans tbl_Main à chaque logging de Keyword où il y a une correspondance. Assurez-vous de créer un index sur le champ de text pour le mot key (par exemple, la colonne Keyword.KeywordText , ou peu importe Keyword.KeywordText vous l'appelez), ainsi que le champ KeywordID dans la table tbl_Main_Keyword . Créer des foreign keys entre les tables.
  2. Écrivez du DML (ou utilisez un programme séparé, tel qu'un programme C #) pour parcourir chaque logging, parsingr le text et insert chaque mot key distinct rencontré dans la table Keyword . Créez une relation avec la ligne pour chaque mot-key dans l'logging tbl_main .
  3. Maintenant, pour la search, parsingr le text de search en mots-keys et composer une requête sur la table tbl_Main_Keyword contenant à la fois une WHERE KeywordID IN et WHERE KeywordID NOT IN , selon qu'il existe une correspondance.

Prenez note de considérer si le cas de chaque mot key est important pour votre parsing de rentabilisation, et considérez le classment (sensible à la casse ou insensible) en conséquence.

Avec un tel design, vous auriez deux tables. Un qui définit les ID et une sous-table qui contient l'set de mots-keys par string de search.

De même, vous devez transformer les strings de search en deux tables, une pour les strings qui doivent correspondre et une pour les strings niées. En supposant que vous mettez cela dans une procédure stockée, ces tables seraient des parameters de valeur de table.

Une fois que vous avez cette configuration, la requête est simple à écrire: 

 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))

Je préférerais la solution de cha, mais voici une autre 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 '%,' + subssortingng(q, 2, 1000) + ',%') group by AS_ID having COUNT(*) = (select COUNT(*) from @QueryParts)