Click here to Skip to main content
15,943,799 members
Please Sign up or sign in to vote.
0.00/5 (No votes)

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

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
 FOREIGN KEY (ProductId) REFERENCES Product(ProductId)

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
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,
from Product P
LEFT OUTER JOIN TagsByProduct TBP ON P.ProductId=TBP.ProductId
-- 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,
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