Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

sql search

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
JCWEBHOST
Asked:
JCWEBHOST
  • 5
  • 3
  • 2
  • +2
1 Solution
 
stergiumCommented:
Hello.
if i understood correctly you meant for something like this

select * from products where tags like '%welding%'
0
 
JCWEBHOSTAuthor Commented:
Yes, my text in tags goes like this

mig torches,mig welding machines,welding
0
 
JCWEBHOSTAuthor Commented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Kamaraj SubramanianApplication Support AnalystCommented:
select * from products where lower(tags) like '%welding%'
0
 
JCWEBHOSTAuthor Commented:
what about the comma? ,
0
 
JCWEBHOSTAuthor Commented:
i want full word matches
0
 
Kamaraj SubramanianApplication Support AnalystCommented:
your column has the value as "mig torches,mig welding machines,welding"

if your user enters "welding", then what output you expect?
0
 
Ioannis ParaskevopoulosCommented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
uaexpertCommented:
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
 
JCWEBHOSTAuthor Commented:
thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now