Solved

sql search

Posted on 2013-02-04
12
282 Views
Last Modified: 2013-02-07
hey guy i a table in sql where the client enter in the product detail, like name price and type.

now i have made one section where he enters in tags like welding, tourchs, gloves

now when he enters a new product he can select the tages or have no tages

now in my products table i have tags has my coloum name and this has text

mig torches,mig welding machines,welding

now i need to do a search to select the matching product to the tags.

please help
0
Comment
Question by:JCWEBHOST
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 13

Expert Comment

by:stergium
ID: 38850209
Hello.
if i understood correctly you meant for something like this

select * from products where tags like '%welding%'
0
 

Author Comment

by:JCWEBHOST
ID: 38850216
Yes, my text in tags goes like this

mig torches,mig welding machines,welding
0
 

Author Comment

by:JCWEBHOST
ID: 38850218
so if the user enters

mig torches in any case it should search in products

but my problem is the comma

mig torches,mig welding machines,welding
0
Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

 
LVL 23

Expert Comment

by:Kamaraj Subramanian
ID: 38850247
select * from products where lower(tags) like '%welding%'
0
 

Author Comment

by:JCWEBHOST
ID: 38850254
what about the comma? ,
0
 

Author Comment

by:JCWEBHOST
ID: 38850260
i want full word matches
0
 
LVL 23

Expert Comment

by:Kamaraj Subramanian
ID: 38850266
your column has the value as "mig torches,mig welding machines,welding"

if your user enters "welding", then what output you expect?
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38850272
Hi,

Try the following

DECLARE @SearchText VARCHAR(100)
SET @SearchText = 'welding'
select * from products 
where lower(tags) like '%,' + @SearchText + '%'
or lower(tags) like '%' + @SearchText + ',%'
or lower(tags) like '%,' + @SearchText + ',%'
or lower(tags) = ' + @SearchText + '

Open in new window


Giannis
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38850280
You need to make sure that all spaces between comas and your tags are trimed though. For instance if the client enters a product with a tag of ' welding ' you should make it 'welding' (stripping away the spaces before and after.
0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 500 total points
ID: 38850327
Sorry, i found an error in the above example

DECLARE @SearchText VARCHAR(100)
SET @SearchText = 'welding'
select * from products 
where lower(tags) like '%,' + @SearchText --Ends with the search text
or lower(tags) like @SearchText + ',%'        --Starts with the search text
or lower(tags) like '%,' + @SearchText + ',%' --The search text is somewhere in the middle
or lower(tags) = @SearchText --Your search text is exactly the same with the tags column

Open in new window



Yet another fix. It should either start with your search text, or end with it or be in the middle or be alone.

Giannis
0
 
LVL 2

Expert Comment

by:uaexpert
ID: 38858482
I suppose you need some query building UI component which help your users to design their own queries. There are plenty of them on the market, some are even free.
Try to google for "query builder component for asp.net" or something similar.
0
 

Author Closing Comment

by:JCWEBHOST
ID: 38866971
thanks
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question