自动完成代码中的sql查询，用于过滤行中匹配的单词。 [英] sql query in auto-complete code to filter a matching word from row.

问题描述

我在sql中有一个表有一个searchtags列，它包含产品的搜索标签。现在，此列可以为产品提供多个搜索标记。说连续产品有ID P001它有许多标签，比如#goodcar #carfrom_honda＃used_2-years＃sell-in-low-price #contactsoon。



现在有一个文本框，其中包含自动完成的代码，当用户在文本框中键入c时，我的查询建议所有这些（#goodcar #carfrom_honda＃used_2-years＃sell-in-low-price #contactsoon。）搜索标签。但我只想要搜索标签c。原因是所有搜索标签都存储在同一行中。如何解决它。请建议。









提前致谢!!!! !!!!!!!!!!!!!!

解决方案

您还没有明确表示您是否希望自动完成仅包含搜索P001或所有可能的搜索标签的标签。但是原理是相同的......你需要将列拆分成单独的标签 - 例如进入临时表，然后从返回结果，其中标签如'c％'。

我不打算详细了解如何做到这一点，因为它是一个非常糟糕的设计，虽然如果你需要拆分功能我推荐一个来自 sqlservercentral [ ^ ]。



为什么这是一个糟糕的设计？您正在努力使用列中的数据这一事实是一个线索。您还限制了每个产品可以使用的搜索标签数量与您存储它们的列的长度。

您还将在产品之间复制数据 - 例如'#carfrom_Honda'可能会出现数千次。您需要应用规范化 [ ^ ] - 查看Michael_Davies的评论



这是他的更详细的建议。



我将首先提供一个简单的产品表

   -   每种产品的所有详细信息 
   -   < span class =code-comment>排除searchtags  
 创建  table 产品
（
 ProductId  int   identity （ 1 ， 1 ） PRIMARY   KEY ，
 description  varchar （ 30 ）
）
 
 将 插入产品值 
（'  Product1'），
（'  Product2'）

需要注意的事项

- 我已经做了 ProductId 一个 int - 在以后搜索时会更有效率，占用更少的磁盘空间而且我不限制适合的产品数量转换为P999格式。

- 它也是一个IDENTITY列 - 这意味着我正在让SQL尽一切努力计算出下一个独特的产品编号 - 如果有的话，特别方便这个表的多个用户。



接下来我将在他们自己的表中提供一个简单的搜索标签列表。我不需要'＃'分隔符，因为每个标记都有自己的行。

  create  < span class =code-keyword> table  SearchTag 
（
 TagId  int  身份（ 1 ， 1 ） PRIMARY   KEY ，
 SearchTag  varchar （ 30 ）
）
 
 插入 进入 SearchTag 值 
（'  goodcar'），
（'  carfrom_honda'），
（  used_2-years'），
（' 低价出售），
（'  contactsoon' ）

我会暂停 - 您现在可以通过简单查询

获取所有可能搜索标签的列表 SELECT  SearchTag  FROM  SearchTag 

允许UI控件中的自动完成功能处理过滤。



现在我将创建一个表格，将搜索标签与特定产品相关联

 创建 表 TagsByProduct 
（
 ProductId  int ， 
 TagId  int  
）
  ALTER   TABLE  TagsByProduct  ADD   CONSTRAINT  fk_Products 
  FOREIGN   KEY （ProductId） REFERENCES 产品（ProductId）
 
  ALTER  表 TagsByProduct  ADD  < span class =code-keyword> CONSTRAINT  fk_SearchTags 
  FOREIGN   KEY （ TagId） REFERENCES  SearchTag（TagId）

我现在可以添加尽可能多的搜索标签。例如。如果我将一些数据放入产品P001

 插入 进入 TagsByProduct  values  
（ 1 ， 1 ） ，
（ 1 ， 2 ），
（ 1 ， 3 ），
（ 1 ， 4 ），
（ 1 ， 5 ）

我可以有一个存储过程返回Product P001的所有搜索标签

   -   < span class =code-comment>如果这是在SP中，那么声明是SP的一部分 
 声明  @ input   varchar （ 5 ）= '  P001' 
 
   -    SP的正文 
  DECLARE   @ id   int  = CAST（SUBSTRING（ @ input ， 2 ，LEN（< span class =code-sdkkeyword> @ input ）） as   int ）
 
 选择 '  P' +  RIGHT （'  0000' +  CONVERT （ nvarchar ，P.ProductId）， 4 ） AS  ProdID，
 P.description，
 S.SearchTag 
 来自产品P 
  LEFT   OUTER   JOIN  TagsByProduct TBP  ON  P.ProductId = TBP.ProductId 
  LEFT   OUTER   JOIN  SearchTag S  ON  TBP.TagId = S.TagId 
   -   如果您只对具有搜索标签的产品感兴趣 
   -  -   然后将JOIN更改为INNER  
 
  WHERE  P. ProductId =  @ id  
   -   如果你对所有标签感兴趣然后删除上面的WHERE  

返回

 P0001 Product1 goodcar 
 P0001 Product1 carfrom_honda 
 P0001 Product1 used_2-years 
 P0001产品1低价出售
 P0001产品1 contactsoon 

虽然您更有可能想要与搜索标签匹配的产品，例如

  set   @ input  =  '  carfrom_honda' 
 
  select  '  P' +  RIGHT （'  0000' +  CONVERT （ nvarchar ，P.ProductId）， 4 ） AS  ProdID，
 P. description 
 来自产品P 
  INNER   JOIN  TagsByProduct TBP  ON  P.ProductId = TBP.ProductId 
  INNER  < span class =code-keyword> JOIN  SearchTag S  ON  TBP.TagId = S.TagId 
  WHERE  s.SearchTag =  @ input  

下一步将搜索多个搜索标签，但我认为这篇文章足够长，所以我现在就把它留在这里。 ： - ）

Hi,


I have one table in sql having a column "searchtags" it contains search tags of a product. Now this column can have multiple search tags for a product . say there is product in a row having id P001 it has many tags like #goodcar #carfrom_honda #used_2-years #selling-in-low-price #contactsoon.

Now there is a textbox having code for auto-complete when user types c in text box my query suggests all these (#goodcar #carfrom_honda #used_2-years #selling-in-low-price #contactsoon.) search tags. but i want only search tags having "c". the reason is that all search tags are stored in same row . how to solve it. pls suggest.




Thanks in advance !!!!!!!!!!!!!!!!!!

解决方案

You haven't made it clear whether you want the auto-complete to contain just the search tags for P001 or all possible search tags. However the principle would be the same ... you need to split the column into the separate tags - e.g. into a temporary table and then return the results from that where tag like 'c%'.
I'm not going to go into full details of how to do this because it is a very bad design, although if you ever need a split function I recommend the one from sqlservercentral[^].

Why is it a bad design? The fact you are struggling to use the data in the column is one clue. You have also limited the number of search tags each product can have to the length of the column in which you store them.
You will also be duplicating data across Products - e.g. '#carfrom_Honda' could appear thousands of times. You need to apply normalization[^] - See the comment from Michael_Davies

Here is his suggestion in more detail.

I'll present a simple Product table first

-- all of the details for each product
-- EXCLUDING searchtags
create table Product
(
	ProductId int identity(1,1) PRIMARY KEY,
	description varchar(30)
)

insert into Product values
('Product1'),
('Product2')

Couple of things to note
-- I've made the ProductId an int - it's going to be far more efficient when it comes to searching later, uses up less disk space and I'm not limiting the number of products that will fit into the P999 format.
-- It's also an IDENTITY column - which means I'm making SQL do all of the effort in working out what the next unique product number will be - especially handy if there are multiple users of this table.

Next I'll present a simple list of Search Tags in their own table. I don't need the '#' separator as each tag has it's own row.

create table SearchTag
(
	TagId int identity(1,1) PRIMARY KEY,
	SearchTag varchar(30)
)

insert into SearchTag values
('goodcar'),
('carfrom_honda'),
('used_2-years'),
('selling-in-low-price'),
('contactsoon')

I'll just pause there - you can now get a list of all possible search tags by the simple query

SELECT SearchTag FROM SearchTag

Allow the auto-complete functionality in your UI control to handle the filtering.

Now I'll create a table that will associate search tags with specific products

create table TagsByProduct
(
	ProductId int,
	TagId int
) 
ALTER TABLE TagsByProduct ADD CONSTRAINT fk_Products
 FOREIGN KEY (ProductId) REFERENCES Product(ProductId)

ALTER TABLE TagsByProduct ADD CONSTRAINT fk_SearchTags
 FOREIGN KEY (TagId) REFERENCES SearchTag(TagId)

I can now add as many search tags as I like to as many products as I like. E.g. if I put some data in for Product P001

insert into TagsByProduct values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5)

I can have a stored procedure that returns all of the search tags for Product P001

--if this is in an SP then the declare is part of the SP
declare @input varchar(5) = 'P001'

--Body of SP
DECLARE @id int = CAST(SUBSTRING(@input,2,LEN(@input)) as int)

select 'P' + RIGHT('0000' + CONVERT(nvarchar, P.ProductId),4) AS ProdID,
P.description,
S.SearchTag
from Product P
LEFT OUTER JOIN TagsByProduct TBP ON P.ProductId=TBP.ProductId
LEFT OUTER JOIN SearchTag S ON TBP.TagId = S.TagId
-- If you are only interested in products that have search tags
-- then change the JOINs to INNER

WHERE P.ProductId = @id
-- If you are interested in all tags then remove the WHERE above

which returns

P0001	Product1	goodcar
P0001	Product1	carfrom_honda
P0001	Product1	used_2-years
P0001	Product1	selling-in-low-price
P0001	Product1	contactsoon

Although you are more likely to want Products that match a search tag e.g.

set @input = 'carfrom_honda'

select 'P' + RIGHT('0000' + CONVERT(nvarchar, P.ProductId),4) AS ProdID,
P.description
from Product P
INNER JOIN TagsByProduct TBP ON P.ProductId=TBP.ProductId
INNER JOIN SearchTag S ON TBP.TagId = S.TagId
WHERE s.SearchTag = @input

The next step will be searching for more than one search tag but I think this post is long enough so I'll leave this here for now. :-)

这篇关于自动完成代码中的sql查询，用于过滤行中匹配的单词。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！