Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

sql like query

hey guys i am having a problem with my search query

if i type this text in my text box for search : "welding inverter"

no results found

but if i put :"welding" or inverter

it bring up results

here is my part of my query

m2.title LIKE '%" + keyword.Replace(" ", "%") + "%'
0
JCWEBHOST
Asked:
JCWEBHOST
  • 11
  • 9
  • 2
  • +1
1 Solution
 
Habib PourfardSoftware DeveloperCommented:
m2.title LIKE '%" + keyword.Trim().Replace(" ", "%' OR m2.title LIKE '%") + "%'
0
 
appariCommented:
debug the source code and post the sql after replacing "%" in place of  " ".

check if the keyword is ending with " ", try trimming before replace as follows:

m2.title LIKE '%" + keyword.trim().Replace(" ", "%") + "%'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
LIKE will only compare to the full string.
if you want to compare to each part, based on 1 input (variable), check out this article
http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html
to get the parmsToList function, could would then be like this:
declare @input varchar(1000)
set @input = 'wedding inverter'
select ...
 from yourtable m2
  join dbo.ParmsToList( @input, ',' ) f
where  m2.title LIKE '%' + f.value + '%'  

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JCWEBHOSTAuthor Commented:
guys try it online it not working

http://www.gas-works.co.za/
0
 
JCWEBHOSTAuthor Commented:
this i my current sql query

SELECT p.id, p_name, SUBSTRING(p.p_name, 0, 10) as s_name FROM  products p inner JOIN menu as m3 ON m3.id = p.menu_id inner JOIN menu as m2 on m2.id = m3.parent_id inner JOIN menu as m1 on m1.id = m2.parent_id WHERE m1.title LIKE '%" + keyword + "%' OR m2.title LIKE '%" + keyword + "%' OR m3.title LIKE '%" + keyword + "%' OR p.p_name LIKE '%" + keyword + "%' OR p.p_code LIKE '%" + keyword + "%'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you try my method?
0
 
JCWEBHOSTAuthor Commented:
how to i implement that code
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I posted the sql script extract, and the function's code used is in the article I linked.
what is the problem, please?
0
 
Habib PourfardSoftware DeveloperCommented:
SELECT  p.id
       ,p_name
       ,SUBSTRING(p.p_name, 0, 10) AS s_name
FROM    products p
INNER JOIN menu AS m3 ON m3.id = p.menu_id
INNER JOIN menu AS m2 ON m2.id = m3.parent_id
INNER JOIN menu AS m1 ON m1.id = m2.parent_id
WHERE   m1.title LIKE '%' + keyword + '%'
        OR m2.title LIKE '%' + keyword + '%'
        OR m3.title LIKE '%' + keyword + '%'
        OR p.p_name LIKE '%' + keyword + '%'
        OR p.p_code LIKE '%' + keyword + '%'

Open in new window

0
 
JCWEBHOSTAuthor Commented:
i tried this but sql query where is incorrect

SELECT p.id, p_name, SUBSTRING(p.p_name, 0, 10) as s_name FROM  products p inner JOIN menu as m3 ON m3.id = p.menu_id inner JOIN menu as m2 on m2.id = m3.parent_id inner JOIN menu as m1 on m1.id = m2.parent_id join dbo.ParmsToList( '%" + keyword + "%', ',' ) f where  m2.title LIKE '%' + f.value + '%' OR m2.title join dbo.ParmsToList( '%" + keyword + "%', ',' ) f where  m2.title LIKE '%' + f.value + '%' OR m3.title join dbo.ParmsToList( '%" + keyword + "%', ',' ) f where  m2.title LIKE '%' + f.value + '%' OR p.p_name join dbo.ParmsToList( '%" + keyword + "%', ',' ) f where  m2.title LIKE '%' + f.value + '%' OR p.p_code join dbo.ParmsToList( '%" + keyword + "%', ',' ) f where  m2.title LIKE '%' + f.value + '%'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
SELECT p.id, p_name, SUBSTRING(p.p_name, 0, 10) as s_name 
FROM  products p 
JOIN menu as m3 ON m3.id = p.menu_id 
JOIN menu as m2 on m2.id = m3.parent_id 
JOIN menu as m1 on m1.id = m2.parent_id 
JOIN dbo.ParmsToList( '%" + keyword + "%', ',' ) f 
  ON m2.title LIKE '%' + f.value + '%' 
  OR m3.title LIKE '%' + f.value + '%'  
  OR m1.title LIKE '%' + f.value + '%'  
  OR p.p_name LIKE '%' + f.value + '%'  
  OR p.p_code LIKE '%' + f.value + '%'  

Open in new window

0
 
JCWEBHOSTAuthor Commented:
pourfard your code do not work it does not find any records
0
 
JCWEBHOSTAuthor Commented:
angelIII your last code do not work to. No recordes found
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is there any of the fields that may be null?

can you show the output of this query:
SELECT p.id, p_name, SUBSTRING(p.p_name, 0, 10) as s_name , f.value
FROM  products p 
JOIN menu as m3 ON m3.id = p.menu_id 
JOIN menu as m2 on m2.id = m3.parent_id 
JOIN menu as m1 on m1.id = m2.parent_id 
LEFT JOIN dbo.ParmsToList( '%" + keyword + "%', ',' ) f  ON 1 = 1 

Open in new window

0
 
JCWEBHOSTAuthor Commented:
The following error occured while executing the query:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.ParmsToList'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you create the function? it's not a build-in function
0
 
JCWEBHOSTAuthor Commented:
i never create. how do i create it?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have the create function code in the article
0
 
JCWEBHOSTAuthor Commented:
ok here is the sql query

SELECT p.id, p_name, SUBSTRING(p.p_name, 0, 10) as s_name , f.value
FROM  products p
JOIN menu as m3 ON m3.id = p.menu_id
JOIN menu as m2 on m2.id = m3.parent_id
JOIN menu as m1 on m1.id = m2.parent_id
LEFT JOIN dbo.ParmsToList( '%" + keyword + "%', ',' ) f  ON 1 = 1


and here are some of the output


id p_name s_name value
579 Bass Broom 300 mm / 380 mm  Bass Broo %" + keyword + "%
580 Platform Broom STIFF With Wooden Handle Platform  %" + keyword + "%
581 Yard Broom With Handle Yard Broo %" + keyword + "%
652 Gutter Sweeper Stiff or Soft with Handles Gutter Sw %" + keyword + "%
725 Testing Testing %" + keyword + "%
814 HIGH SPEED DRILL BITS HIGH SPEE %" + keyword + "%
808 OPEN AND RING SPANNER OPEN AND  %" + keyword + "%
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
"keyword" should be the keyword you put ....
SELECT p.id, p_name, SUBSTRING(p.p_name, 0, 10) as s_name , f.value
FROM  products p
JOIN menu as m3 ON m3.id = p.menu_id
JOIN menu as m2 on m2.id = m3.parent_id
JOIN menu as m1 on m1.id = m2.parent_id
LEFT JOIN dbo.ParmsToList( 'welding inverter', ',' ) f  ON 1 = 1  

Open in new window


hence:
SELECT p.id, p_name, SUBSTRING(p.p_name, 0, 10) as s_name 
FROM  products p 
JOIN menu as m3 ON m3.id = p.menu_id 
JOIN menu as m2 on m2.id = m3.parent_id 
JOIN menu as m1 on m1.id = m2.parent_id 
JOIN dbo.ParmsToList( 'welding inverter', ',' ) f 
  ON m2.title LIKE '%' + f.value + '%' 
  OR m3.title LIKE '%' + f.value + '%'  
  OR m1.title LIKE '%' + f.value + '%'  
  OR p.p_name LIKE '%' + f.value + '%'  
  OR p.p_code LIKE '%' + f.value + '%'  
                                            

Open in new window

0
 
JCWEBHOSTAuthor Commented:
sorry i am lost, i have used this query

SELECT p.id, p_name, SUBSTRING(p.p_name, 0, 10) as s_name
FROM  products p
JOIN menu as m3 ON m3.id = p.menu_id
JOIN menu as m2 on m2.id = m3.parent_id
JOIN menu as m1 on m1.id = m2.parent_id
JOIN dbo.ParmsToList( 'welding inverter', ',' ) f
  ON m2.title LIKE '%' + f.value + '%'
  OR m3.title LIKE '%' + f.value + '%'  
  OR m1.title LIKE '%' + f.value + '%'  
  OR p.p_name LIKE '%' + f.value + '%'  
  OR p.p_code LIKE '%' + f.value + '%'


but no results found for welding inverter

but if i use welding or inverter single word it returns values

please help
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, my fault. the "split" character is space, not ,

SELECT p.id, p_name, SUBSTRING(p.p_name, 0, 10) as s_name
FROM  products p
JOIN menu as m3 ON m3.id = p.menu_id
JOIN menu as m2 on m2.id = m3.parent_id
JOIN menu as m1 on m1.id = m2.parent_id
JOIN dbo.ParmsToList( 'welding,inverter', ',' ) f
  ON m2.title LIKE '%' + f.value + '%'
  OR m3.title LIKE '%' + f.value + '%'  
  OR m1.title LIKE '%' + f.value + '%'  
  OR p.p_name LIKE '%' + f.value + '%'  
  OR p.p_code LIKE '%' + f.value + '%'

Open in new window


should work
0
 
JCWEBHOSTAuthor Commented:
thanks
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 11
  • 9
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now