Click here to Skip to main content
15,943,799 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 !!!!!!!!!!!!!!!!!!
Posted
Comments
Michael_Davies 23-Jul-15 2:06am    
Why not store the tags in a separate table and use a many-to-many relationship, that way you only store the tag once and can easily find them beginning with, in this case, "c" and the find all the matching "other" records from the relation. Advantage is you can perform the search easily, you only store the tag once and have no limit on how many tags an "other" record has.

1 solution

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
SQL
-- 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.
SQL
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
SQL
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
SQL
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
SQL
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
SQL
--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.
SQL
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. :-)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900